How to search for nulls with MS SQL Server
Summary
- Normally, the SQL engine does not display NULL, when there is no value entered, except for MS SQL Server that always display the word NULL.
- Nulls are not included in calculations or summary operations.
You must either use:
- case statements
- coalesce
Data used
select empno, firstnme, midinit as mid, lastname, workdept as dept,
phoneno as phone, hiredate as hire, job, edlevel as education,
sex, birthdate, salary, bonus, comm as commission
from emps;
empno firstnme mid lastname dept phone hire job education sex birthdate salary bonus commission
------ ------------ ---- --------------- ---- ----- ----------------------- -------- --------- ---- ----------------------- --------------------------------------- --------------------------------------- ---------------------------------------
000010 CHRISTINE I HAAS A00 3978 1980-01-01 00:00:00.000 PRES 18 F 1950-03-24 00:00:00.000 52750.00 1000.00 4220.00
000020 MICHAEL L THOMPSON NULL 3476 1988-10-10 00:00:00.000 MANAGER 18 M 1964-09-02 00:00:00.000 NULL NULL NULL
000030 SALLY! A KWAN C01 4738 1990-04-05 00:00:00.000 MANAGER 20 F 1957-12-11 00:00:00.000 38250.00 800.00 3060.00
000050 JOHN B GEYER E01 6789 1964-08-17 00:00:00.000 MANAGER 16 M 1942-04-15 00:00:00.000 40175.00 800.00 3214.00
000060 IRVING@ F STERN D11 6423 1988-09-14 00:00:00.000 MANAGER 16 M 1962-02-07 00:00:00.000 32250.00 500.00 2580.00
000070 EVA D PULASKI D21 7831 1995-09-30 00:00:00.000 MANAGER 16 F 1969-12-26 00:00:00.000 36170.00 700.00 2893.00
000090 EILEEN# W HENDERSON E11 5498 1985-08-15 00:00:00.000 MANAGER 16 F 1957-12-15 00:00:00.000 29750.00 600.00 2380.00
000100 THEODORE Q SPENSER NULL NULL 1995-06-19 00:00:00.000 MANAGER 14 M 1973-07-18 00:00:00.000 NULL NULL NULL
000110 VINCENZO. G LUCCHESSI A00 3490 1973-05-16 00:00:00.000 SALESREP 19 M 1946-06-05 00:00:00.000 46500.00 900.00 3720.00
000120 SEAN O CONNELL A00 2167 1978-12-05 00:00:00.000 CLERK 14 M 1959-05-18 00:00:00.000 29250.00 600.00 2340.00
000130 DOLORES, M QUINTANA C01 4578 1986-07-28 00:00:00.000 ANALYST 16 F 1942-04-15 00:00:00.000 23800.00 500.00 1904.00
000140 HEATHER A NICHOLLS C01 1793 1991-12-15 00:00:00.000 ANALYST 18 F 1962-08-19 00:00:00.000 28420.00 600.00 2274.00
000150 BRUCE ADAMSON NULL NULL 1987-02-12 00:00:00.000 DESIGNER 16 M 1963-12-17 00:00:00.000 NULL NULL NULL
000160 ELIZABETH R PIANKA D11 3782 1992-10-11 00:00:00.000 DESIGNER 17 F 1971-11-12 00:00:00.000 22250.00 400.00 1780.00
000170 MASATOSHI% J YOSHIMURA D11 2890 1993-09-15 00:00:00.000 DESIGNER 16 M 1967-08-05 00:00:00.000 24680.00 500.00 1974.00
000180 MARILYN S SCOUTTEN D11 1682 1988-07-07 00:00:00.000 DESIGNER 17 F 1965-09-21 00:00:00.000 21340.00 500.00 1707.00
000190 JAMES& H WALKER D11 2986 1989-07-26 00:00:00.000 DESIGNER 16 M 1969-01-25 00:00:00.000 20450.00 400.00 1636.00
000200 DAVID BROWN D11 4501 1981-03-03 00:00:00.000 DESIGNER 16 M 1957-12-29 00:00:00.000 27740.00 600.00 2217.00
000210 WILLIAM* T JONES D11 0942 1994-04-11 00:00:00.000 DESIGNER 17 M 1969-09-23 00:00:00.000 18270.00 400.00 1462.00
000220 JENNIFER] K LUTZ NULL NULL 1983-08-29 00:00:00.000 DESIGNER 18 F 1964-10-19 00:00:00.000 NULL NULL NULL
000230 JAMES J JEFFERSON D21 2094 1981-11-21 00:00:00.000 CLERK 14 M 1951-12-30 00:00:00.000 22180.00 400.00 1774.00
000240 SALVATORE[ M MARINO D21 3780 1994-12-05 00:00:00.000 CLERK 17 M 1970-10-31 00:00:00.000 28760.00 600.00 2301.00
000250 DANIEL S SMITH D21 0961 1984-10-30 00:00:00.000 CLERK 15 M 1956-06-12 00:00:00.000 19180.00 400.00 1534.00
000260 SYBIL\ P JOHNSON D21 8953 1990-09-11 00:00:00.000 CLERK 16 F 1953-05-05 00:00:00.000 17250.00 300.00 1380.00
000270 MARIA L PEREZ D21 9001 1995-09-30 00:00:00.000 CLERK 15 F 1969-12-26 00:00:00.000 27380.00 500.00 2190.00
000280 ETHEL/ R SCHNEIDER E11 8997 1982-03-24 00:00:00.000 OPERATOR 17 F 1952-10-28 00:00:00.000 26250.00 500.00 2100.00
000290 JOHN R PARKER E11 4502 1995-05-30 00:00:00.000 OPERATOR 12 M 1963-02-09 00:00:00.000 15340.00 300.00 1227.00
000300 PHILIP X SMITH E11 2095 1987-06-19 00:00:00.000 OPERATOR 14 M 1953-05-27 00:00:00.000 17750.00 400.00 1420.00
000310 MAUDE F SETRIGHT E11 3332 1979-09-12 00:00:00.000 OPERATOR 12 F 1947-11-21 00:00:00.000 15900.00 300.00 1272.00
000320 RAMLAL V MEHTA NULL 9990 1980-07-07 00:00:00.000 FIELDREP 16 M 1949-03-11 00:00:00.000 19950.00 400.00 1596.00
000330 WING LEE E21 2103 1991-02-23 00:00:00.000 FIELDREP 14 M 1958-02-18 00:00:00.000 25370.00 500.00 2030.00
000340 JASON R GOUNOT E21 5698 1962-05-05 00:00:00.000 FIELDREP 16 M 1942-12-17 00:00:00.000 23840.00 500.00 1907.00
(32 row(s) affected)
What the difference between zero versus null or blank versus null?
- Zero has a 0 value. We know it's value, it's zero!
- Null is unknown. We don't know it's value yet, it could be a big number, a small number (positive or negative or even zero) but we don't have that information.
Sex, is often coded wrong, it's usually coded as a 2 state: Male -- M or Female -- F. It should be coded with 3 states: Male -- M, Female -- F, Unknown -- Null.
Kim as a first name is it Male or Female? That depends on the country and the culture. In North America, it's mostly female, in Northern Europe, it's mostly male. Therefore you can tell the sex of the person from the first name, that's why you need the NULL for unknown.
Solution 1: coalesce
select empno, firstnme, lastname,
coalesce(workdept,'Not working yet') as dept,
coalesce(job,'NA') as job,
coalesce(salary,0) as salary,
coalesce(bonus,0) as bonus,
coalesce(comm,0) as commission
from emps
where workdept is null;
empno firstnme lastname dept job salary bonus commission
------ ------------ --------------- --------------- -------- --------------------------------------- --------------------------------------- ---------------------------------------
000020 MICHAEL THOMPSON Not working yet MANAGER 0.00 0.00 0.00
000100 THEODORE SPENSER Not working yet MANAGER 0.00 0.00 0.00
000150 BRUCE ADAMSON Not working yet DESIGNER 0.00 0.00 0.00
000220 JENNIFER] LUTZ Not working yet DESIGNER 0.00 0.00 0.00
000320 RAMLAL MEHTA Not working yet FIELDREP 19950.00 400.00 1596.00
(5 row(s) affected)
- The coalesce statement will return the first non-null value for the column.
- Coalesce does NOT allow for data type conversion.
Here is what happens with MS SQL Server when you try to convert bonus from a number to a string: NA if the bonus is null:
select empno, firstnme, lastname,
coalesce(workdept,'Not working yet'),
coalesce(bonus,'NA')
from emps
where workdept is null;
empno firstnme lastname
------ ------------ --------------- --------------- ---------------------------------------
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Solution 2: case statement
select empno, firstnme, lastname,
case when workdept is null then 'Not working yet' else workdept end as dept,
case when job is null then 'NA' else job end as job,
case when salary is null then -9999 else salary end as salary,
case when bonus is null then 0 else bonus end as bonus,
case when comm is null then 0 else comm end as commission
from emps
where workdept is null;
empno firstnme lastname dept job salary bonus commission
------ ------------ --------------- --------------- -------- --------------------------------------- --------------------------------------- ---------------------------------------
000020 MICHAEL THOMPSON Not working yet MANAGER -9999.00 0.00 0.00
000100 THEODORE SPENSER Not working yet MANAGER -9999.00 0.00 0.00
000150 BRUCE ADAMSON Not working yet DESIGNER -9999.00 0.00 0.00
000220 JENNIFER] LUTZ Not working yet DESIGNER -9999.00 0.00 0.00
000320 RAMLAL MEHTA Not working yet FIELDREP 19950.00 400.00 1596.00
(5 row(s) affected)
- There is no discernible difference in speed with MS SQL Server, the analysis of the access plan is identical for both methods.
- Coalesce is usually easier to read, but it's a personal preference.