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

sql
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:

sql
SELECT name
  FROM $planets
 WHERE name = null;

Returns an empty set. This holds for inequality too:

sql
SELECT name
  FROM $planets
 WHERE name != null;

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

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:

sql
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;