NULL, three-valued logic and the T-SQL quirks around it
Why NOT IN can return nothing, how ISNULL truncates, why a UNIQUE column takes one NULL, and IS DISTINCT FROM in SQL Server 2022.
After this lesson you can
- Predict the result of a comparison, NOT IN or <> when NULLs are involved
- Choose between ISNULL, COALESCE, + and CONCAT knowing what each does to types and NULLs
- Allow several NULLs in a unique column with a filtered index
NULL means "no value known". Any comparison with it, including
NULL = NULL, is neither true nor false but unknown, and WHERE
keeps only rows where the condition is true. That one rule explains
most NULL bugs.
SQL Server makes this depend on a session setting, ANSI_NULLS. It is
ON for every modern client, OFF is deprecated, and indexed views and
filtered indexes refuse to work without it. Write IS NULL and
IS NOT NULL, never = NULL.
NOT IN and a single NULL
x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL.
The last term is unknown, so the whole condition can never be true. A
subquery that returns one NULL makes NOT IN return no rows at all.
NOT EXISTS compares row by row and does not have this problem.
CREATE TABLE customers (id INT PRIMARY KEY, name NVARCHAR(30) NOT NULL);CREATE TABLE blocked (customer_id INT NULL);INSERT INTO customers VALUES (1, N'Nino'), (2, N'Luka'), (3, N'Ana');INSERT INTO blocked VALUES (2), (NULL);SELECT 'NOT IN' AS method, COUNT(*) AS allowed_customersFROM customersWHERE id NOT IN (SELECT customer_id FROM blocked)UNION ALLSELECT 'NOT EXISTS', COUNT(*)FROM customers AS cWHERE NOT EXISTS (SELECT 1 FROM blocked AS b WHERE b.customer_id = c.id);ISNULL, COALESCE, + and CONCAT
ISNULL(a, b)takes the data type ofa. IfaisVARCHAR(3), the replacement is cut to three characters.COALESCE(a, b, ...)is standard SQL and returns the type with the highest precedence among its arguments. It is rewritten as aCASE.a + bon strings yieldsNULLif either side isNULL.CONCAT(a, b)treatsNULLas an empty string.a IS DISTINCT FROM b, new in SQL Server 2022, is a comparison that treats two NULLs as equal and NULL against a value as different. It is never unknown.
CREATE TABLE t (id INT PRIMARY KEY, code VARCHAR(3) NULL);INSERT INTO t VALUES (1, NULL), (2, 'ab');SELECT id, ISNULL(code, 'unknown') AS isnull_v, COALESCE(code, 'unknown') AS coalesce_v, code + 'x' AS plus_v, CONCAT(code, 'x') AS concat_v, CASE WHEN code <> 'ab' THEN 1 ELSE 0 END AS not_equal_ab, CASE WHEN code IS DISTINCT FROM 'ab' THEN 1 ELSE 0 END AS distinct_from_abFROM tORDER BY id;UNIQUE and NULL
The SQL standard lets a UNIQUE column hold many NULLs. SQL Server does
not: a unique constraint or unique index treats NULL as a value, so a
second NULL is a duplicate. To enforce "unique when present", use a
filtered unique index:
CREATE UNIQUE INDEX ux_users_email ON users (email) WHERE email IS NOT NULL;
CREATE TABLE strict_users (id INT PRIMARY KEY, email NVARCHAR(50) NULL UNIQUE);CREATE TABLE filtered_users (id INT PRIMARY KEY, email NVARCHAR(50) NULL);CREATE UNIQUE INDEX ux_filtered_email ON filtered_users (email) WHERE email IS NOT NULL;DECLARE @log TABLE (tbl NVARCHAR(20), outcome NVARCHAR(40)); INSERT INTO strict_users VALUES (1, NULL);BEGIN TRY INSERT INTO strict_users VALUES (2, NULL); INSERT INTO @log VALUES (N'strict_users', N'second NULL accepted');END TRYBEGIN CATCH INSERT INTO @log VALUES (N'strict_users', CONCAT(N'error ', ERROR_NUMBER()));END CATCH; INSERT INTO filtered_users VALUES (1, NULL), (2, NULL);INSERT INTO @log VALUES (N'filtered_users', N'second NULL accepted'); SELECT tbl, outcome FROM @log;Aggregates skip NULLs
COUNT(*) counts rows; COUNT(col), SUM, AVG, MIN and MAX
ignore NULLs. AVG(bonus) is the average of the people who have a
bonus, not of everyone. If a missing value means zero, say so with
AVG(COALESCE(bonus, 0)).
Common mistakes
col <> 'X'to mean "everything except X". Rows wherecolis NULL are dropped too, becauseNULL <> 'X'is unknown.NOT IN (subquery)on a nullable column. One NULL and the result is empty, as the first example shows.ISNULLwith a longer replacement. The second example printedunk, notunknown.- A
UNIQUEconstraint on an optional column. The second customer without an email is rejected with error 2627.
What interviewers ask
Try it yourself
2 visible tests · 2 hidden testsTable orders(id, discount_code), where discount_code is
VARCHAR(5) and is NULL when no code was used.
Return every order that was not placed with the code 'STAFF',
including orders with no code at all. Columns: id, code_label,
where code_label is the discount code, or the text '(none)' when
there is no code. Order by id.
CREATE TABLE orders (id INT PRIMARY KEY, discount_code VARCHAR(5) NULL);INSERT INTO orders VALUES (1, 'STAFF'), (2, 'SPRNG'), (3, 'STAFF'), (4, 'BULK');Sign up to run the tests, check the hidden ones and save your progress. Sign up