Skip to main content

Edit this page

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:

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

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.

Query
SELECT NULL = NULL;
Result
 ?column?---------- NULL
Query
SELECT NULL IS NOT DISTINCT FROM NULL;
Result
 ?column?---------- t
Query
SELECT NULL IS NULL;
Result
 ?column?---------- t

NULL and Functions​

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

Query
SELECT cos(NULL);
Result
 cos------ 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.

Query
SELECT coalesce(NULL, NULL, 1);
Result
 coalesce----------        1
Query
SELECT coalesce(10, 20);
Result
 coalesce----------       10
Query
SELECT coalesce(NULL, NULL);
Result
 coalesce---------- NULL

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

Query
SELECT ifnull(NULL, 'default_string');
Result
 coalesce---------------- default_string
Query
SELECT ifnull(1, 'default_string');
Result
 coalesce----------        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⟩ is different from ... IN ⟨something with no NULLs⟩. 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.

Query
CREATE TABLE integers (i INTEGER);
INSERT INTO integers VALUES (1), (10), (NULL);
Query
SELECT min(i) FROM integers;
Result
 min-----   1
Query
SELECT max(i) FROM integers;
Result
 max-----  10