NULL Values

NULL values are special values that are used to represent missing data in SQL. Columns of any type can contain NULL values. Logically, a NULL value can be seen as “the value of this field is unknown”.

A NULL value can be inserted to any field that does not have the NOT NULL qualifier:

CREATE TABLE integers (i INTEGER);
INSERT INTO integers VALUES (NULL);

NULL values have special semantics in many parts of the query as well as in many functions:

Any comparison with a NULL value returns NULL, including NULL = NULL.

You can use IS NOT DISTINCT FROM to perform an equality comparison where NULL values compare equal to each other. Use IS (NOT) NULL to check if a value is NULL.

SELECT NULL = NULL;

NULL

SELECT NULL IS NOT DISTINCT FROM NULL;

true

SELECT NULL IS NULL;

true

NULL and Functions

A function that has an input argument as NULL usually returns NULL.

SELECT cos(NULL);

NULL

The coalesce function is an exception to this: it takes any number of arguments, and returns for each row the first argument that is not NULL. If all arguments are NULL, coalesce also returns NULL.

SELECT coalesce(NULL, NULL, 1);

1

SELECT coalesce(10, 20);

10

SELECT coalesce(NULL, NULL);

NULL

The ifnull function is a two-argument version of coalesce.

SELECT ifnull(NULL, 'default_string');

default_string

SELECT ifnull(1, 'default_string');

1

NULL and AND / OR

NULL values have special behavior when used with AND and OR. For details, see the Boolean Type documentation.

NULL and IN / NOT IN

The behavior of ... IN ⟨something with a NULL⟩{:.language-sql .highlight} is different from ... IN ⟨something with no NULLs⟩{:.language-sql .highlight}. For details, see the IN documentation.

NULL and Aggregate Functions

NULL values are ignored in most aggregate functions.

Aggregate functions that do not ignore NULL values include: first, last, list and array_agg. To exclude NULL values from those aggregate functions, the FILTER clause can be used.

CREATE TABLE integers (i INTEGER);
INSERT INTO integers VALUES (1), (10), (NULL);

SELECT min(i) FROM integers;

1

SELECT max(i) FROM integers;

10