NULL and three-valued logic

In SQL, a condition can be true, false or unknown (NULL). Any comparison with NULL, including NULL = NULL, gives unknown. WHERE and ON only let through what is true.

SELECT NULL = NULL AS equal,
       NULL <> 1 AS different,
       NULL IS NULL AS is_null,
       1 + NULL AS total,
       'a' || NULL AS concatenated;

equal | different | is_null | total | concatenated
----- | --------- | ------- | ----- | ------------
NULL  | NULL      | 1       | NULL  | NULL

IS NULL, never = NULL

SELECT
  (SELECT COUNT(*) FROM customers WHERE city = NULL) AS with_equals,
  (SELECT COUNT(*) FROM customers WHERE city IS NULL) AS with_is_null;

with_equals | with_is_null
----------- | ------------
0           | 1

The NOT IN trap

x NOT IN (a, b, NULL) is x <> a AND x <> b AND x <> NULL. The last term is unknown, so the whole is never true: the query comes back empty. With NOT EXISTS this does not happen.

SELECT
  (SELECT COUNT(*) FROM customers WHERE city NOT IN ('Recife', NULL)) AS not_in,
  (SELECT COUNT(*) FROM customers c
    WHERE NOT EXISTS (SELECT 1 FROM (SELECT 'Recife' AS x UNION ALL SELECT NULL) l WHERE l.x = c.city)) AS not_exists;

not_in | not_exists
------ | ----------
0      | 5

NOT EXISTS returned 5: the four customers with a city and Elisa, whose city is unknown. Decide whether she belongs; what is not acceptable is the answer changing because the list gained a NULL.

AND and OR with unknown

• FALSE AND NULL is false (the answer is already known); TRUE AND NULL is unknown.
• TRUE OR NULL is true; FALSE OR NULL is unknown.
• NOT NULL is still unknown.

SELECT (1 = 0) AND NULL AS false_and_null,
       (1 = 1) OR NULL AS true_or_null,
       (1 = 1) AND NULL AS true_and_null;

false_and_null | true_or_null | true_and_null
-------------- | ------------ | -------------
0              | 1            | NULL

(SQLite shows true as 1 and false as 0.)

COALESCE and the comparison that treats NULL as a value

COALESCE(a, b, …) returns the first one that is not NULL. To compare treating two NULLs as equal, the standard is IS NOT DISTINCT FROM; in SQLite, IS; in Spark, the <=> operator.

SELECT name, COALESCE(city, '(no city)') AS city,
       city IS NULL AS unknown
FROM customers
WHERE id IN (4, 5);

name  | city      | unknown
----- | --------- | -------
Diego | Curitiba  | 0
Elisa | (no city) | 1

• In ORDER BY ... ASC, SQLite and Spark put NULL first; PostgreSQL puts it last. Write NULLS FIRST or NULLS LAST when the order matters.
• A NULL in a JOIN key never matches anything, not even another NULL.