NULL and three-valued logic

Some BuildMart customers have not given a phone number, an email address or even a city. Those cells hold NULL: a marker that means no value is known. NULL is not zero, and it is not empty text (N''). It is the absence of a value.

1SELECT FirstName, City, Email, Phone2FROM dbo.Customers;

FirstName  City       Email              Phone
---------  ---------  -----------------  -------------
Asha       Leeds      asha@example.com   0113 496 0001
Ben        York       NULL               01904 555 010
Chloe      Leeds      chloe@example.com  NULL
Dev        Bradford   dev@example.com    01274 555 220
Ella       NULL       ella@example.com   NULL
Farid      York       farid@example.com  01904 555 777
Grace      Leeds      NULL               NULL
Zoë        Harrogate  zoe@example.com    01423 555 123

Three-valued logic

Is an unknown city equal to Leeds? SQL Server cannot say yes or no, so the answer is a third truth value: UNKNOWN. Any comparison with NULL (=, <>, <, …) gives UNKNOWN. WHERE keeps a row only when the condition is TRUE, so UNKNOWN rows are dropped, exactly like FALSE ones.

IS NULL, not = NULL

= NULL asks “is this equal to an unknown value?”, which is always UNKNOWN, so this query returns no rows at all, even though three customers have no phone:

1SELECT FirstName, Phone2FROM dbo.Customers3WHERE Phone = NULL;

FirstName  Phone
---------  -----

The right test is IS NULL (or IS NOT NULL). It asks “is a value missing?”, which always has a TRUE or FALSE answer:

1SELECT FirstName, Phone2FROM dbo.Customers3WHERE Phone IS NULL;

FirstName  Phone
---------  -----
Chloe      NULL
Ella       NULL
Grace      NULL

The missing row

Ella’s city is NULL. Ask for customers in Leeds and she is not there. Ask for customers not in Leeds and she is still not there:

1SELECT FirstName, City2FROM dbo.Customers3WHERE City <> N'Leeds';

FirstName  City
---------  ---------
Ben        York
Dev        Bradford
Farid      York
Zoë        Harrogate

Three customers live in Leeds and four live elsewhere, which adds up to 7 of the 8. Whenever a column allows NULL, a condition and its opposite do not cover every row. Decide where the NULL rows belong and say so: WHERE City <> N'Leeds' OR City IS NULL.

NULL spreads through expressions

Arithmetic or + with a NULL gives NULL: 5 plus an unknown number is unknown. CONCAT is the exception; it treats NULL as empty text.

1SELECT 5 + NULL AS AddNull,2       N'Phone: ' + NULL AS PlusNull,3       CONCAT(N'Phone: ', NULL) AS ConcatNull;

AddNull  PlusNull  ConcatNull
-------  --------  ----------
   NULL  NULL      Phone:

Replacing NULL: ISNULL and COALESCE

ISNULL(x, fallback) returns x, or the fallback when x is NULL. COALESCE(a, b, c, …) returns the first value in its list that is not NULL, so it can try several columns in turn:

1SELECT FirstName,2       ISNULL(City, N'(unknown)') AS City,3       COALESCE(Phone, Email, 'no contact details') AS BestContact4FROM dbo.Customers;

FirstName  City       BestContact
---------  ---------  ------------------
Asha       Leeds      0113 496 0001
Ben        York       01904 555 010
Chloe      Leeds      chloe@example.com
Dev        Bradford   01274 555 220
Ella       (unknown)  ella@example.com
Farid      York       01904 555 777
Grace      Leeds      no contact details
Zoë        Harrogate  01423 555 123

Grace has neither a phone nor an email, so COALESCE falls through to the text at the end. COALESCE is standard SQL and takes any number of arguments; ISNULL is T-SQL only and takes exactly two. NULLIF(a, b) goes the other way: it returns NULL when a equals b, which is handy to avoid dividing by zero: x / NULLIF(Stock, 0).

The NOT IN trap

City NOT IN (N'Leeds', NULL) means City <> N'Leeds' AND City <> NULL. The second test is always UNKNOWN, so the whole condition can never be TRUE:

1SELECT FirstName, City2FROM dbo.Customers3WHERE City NOT IN (N'Leeds', NULL);

FirstName  City
---------  ----

1SELECT FirstName, LastName, City, ISNULL(Phone, '(none)') AS Phone2FROM dbo.Customers3WHERE City IS NOT NULL AND Email IS NULL;

FirstName  LastName  City   Phone
---------  --------  -----  -------------
Ben        Carter    York   01904 555 010
Grace      Lee       Leeds  (none)