Try Opteryx

NULL Semantics

Most comparisons involving NULL return NULL, not true or false. The exceptions are the operators built specifically to test for NULL — IS NULL and IS NOT NULL.

In a SELECT, NULL Stays NULL

sqlOpen in Studio
SELECT name = null
  FROM $planets;
 name=null
-----------
 null
 null
 null
 ...

Every row's comparison evaluates to NULL, because name is never actually NULL — but the comparison itself is undefined against a NULL operand, regardless of which side it's on.

In a WHERE, NULL Is Never True

A filter keeps a row only when its condition evaluates to true. NULL is neither true nor false, so rows where the condition evaluates to NULL are dropped:

sqlOpen in Studio
SELECT name
  FROM $planets
 WHERE name = null;

Returns an empty set. This holds for inequality too:

sqlOpen in Studio
SELECT name
  FROM $planets
 WHERE name != null;

Also empty — != null is exactly as undefined as = null.

IN and NOT IN

IN is shorthand for a chain of = joined by OR, and NOT IN for a chain of != joined by AND. A NULL in the list, or as the value being looked for, follows from that expansion:

sql
x IN (1, NULL)      -- x = 1  OR  x = NULL
x NOT IN (1, NULL)  -- x != 1 AND x != NULL

x = NULL and x != NULL are both NULL. A match still decides IN, because true OR NULL is true, but any other row gets NULL, because false OR NULL is NULL:

sqlOpen in Studio
SELECT 1 IN (1, NULL), 2 IN (1, NULL), NULL IN (1, NULL);
 true | null | null

NOT IN turns that around. A match gives false, because false AND NULL is false. Every other row gets NULL, because true AND NULL is NULL, so NOT IN with a NULL in the list is never true:

sqlOpen in Studio
SELECT 1 NOT IN (1, NULL), 2 NOT IN (1, NULL), NULL NOT IN (1, NULL);
 false | null | null

So in a WHERE clause, IN with a NULL in the list keeps the same rows as it would without the NULL. NOT IN with a NULL in the list keeps no rows at all:

sqlOpen in Studio
SELECT name
  FROM $planets
 WHERE id NOT IN (1, 2, NULL);

Returns an empty set. The same holds when the list comes from a subquery that returns a NULL. To exclude values while ignoring missing ones, filter the NULLs out of the list. For a subquery, write NOT IN (SELECT col FROM t WHERE col IS NOT NULL).

A typed NULL such as CAST(NULL AS INTEGER) is allowed in the list, but like any other element it must have the same type as the rest of the list.

Testing for NULL

Because NULL = NULL is itself NULL (not true), an equality test can never find NULL rows. Use IS NULL / IS NOT NULL instead:

sql
SELECT name
  FROM data.observations
 WHERE reading IS NULL;

IS comparisons are evaluated directly rather than going through the three-valued comparison logic, so they give a definite true/false even when the column itself is NULL.

The same holds for the other IS tests, including IS JSON. A NULL is not a well-formed JSON document, so IS JSON returns false for it and IS NOT JSON returns true. Never NULL:

sqlOpen in Studio
SELECT payload IS JSON AS ok
  FROM (VALUES ('{"a": 1}'), ('not json'), (NULL)) AS t(payload);
 ok
-------
 true
 false
 false

IS NOT JSON therefore picks up the NULL rows along with the malformed ones. Add payload IS NOT NULL if you only want the malformed ones.

An untyped NULL literal has no type to test, so SELECT NULL IS JSON is a type error, the same as SELECT NULL IS TRUE. A NULL that comes from a column, or from CAST(NULL AS VARCHAR), behaves as above.

Coalescing Around NULL

IFNULL and COALESCE substitute a value when a column is NULL, which is usually more useful than filtering the row out entirely:

sql
SELECT name, IFNULL(notes, 'no notes recorded') AS notes
  FROM data.observations;