Skip to main content
SereneDBSereneDB
897Contact Us

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:

SQL
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.

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

NULL and Functions​

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

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

SQL
SELECT coalesce(NULL, NULL, 1);
 coalesce
----------
        1
SQL
SELECT coalesce(10, 20);
 coalesce
----------
       10
SQL
SELECT coalesce(NULL, NULL);
 coalesce
----------
 NULL

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

SQL
SELECT ifnull(NULL, 'default_string');
 coalesce
----------------
 default_string
SQL
SELECT ifnull(1, 'default_string');
 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.

SQL
CREATE TABLE integers (i INTEGER);
INSERT INTO integers VALUES (1), (10), (NULL);
SQL
SELECT min(i) FROM integers;
 min
-----
   1
SQL
SELECT max(i) FROM integers;
 max
-----
  10
Edit this page ↗