NULL
is the term used to represent a missing value. A NULL
value in a table is a value in a field that appears to be blank. A field with a NULL
value is a field with no value.
This page summarizes how NULL
values are handled in CockroachDB SQL. Each topic is demonstrated via the built-in SQL client.
When using the built-in client, NULL
values are displayed using the word NULL
. This distinguishes them from a character field that contains an empty string (""
).
NULLs and simple comparisons
Any simple comparison between a value and NULL
results in
NULL
. The remaining cases are described in the next section.
This behavior is consistent with PostgreSQL as well as all other major RDBMS's.
> INSERT INTO customers (customer_id, cust_name, cust_email) VALUES (1, 'Smith', NULL);
> CREATE TABLE t1(
a INT,
b INT,
c INT
);
> INSERT INTO t1 VALUES(1, 0, 0);
> INSERT INTO t1 VALUES(2, 0, 1);
> INSERT INTO t1 VALUES(3, 1, 0);
> INSERT INTO t1 VALUES(4, 1, 1);
> INSERT INTO t1 VALUES(5, NULL, 0);
> INSERT INTO t1 VALUES(6, NULL, 1);
> INSERT INTO t1 VALUES(7, NULL, NULL);
> SELECT * FROM t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 0 | 0 |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
| 5 | NULL | 0 |
| 6 | NULL | 1 |
| 7 | NULL | NULL |
+---+------+------+
> SELECT * FROM t1 WHERE b < 10;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 0 | 0 |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
+---+---+---+
> SELECT * FROM t1 WHERE NOT b > 10;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 0 | 0 |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
+---+---+---+
> SELECT * FROM t1 WHERE b < 10 OR c = 1;
+---+------+---+
| a | b | c |
+---+------+---+
| 1 | 0 | 0 |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
| 6 | NULL | 1 |
+---+------+---+
> SELECT * FROM t1 WHERE b < 10 AND c = 1;
+---+---+---+
| a | b | c |
+---+---+---+
| 2 | 0 | 1 |
| 4 | 1 | 1 |
+---+---+---+
> SELECT * FROM t1 WHERE NOT (b < 10 AND c = 1);
+---+------+---+
| a | b | c |
+---+------+---+
| 1 | 0 | 0 |
| 3 | 1 | 0 |
| 5 | NULL | 0 |
+---+------+---+
> SELECT * FROM t1 WHERE NOT (c = 1 AND b < 10);
+---+------+---+
| a | b | c |
+---+------+---+
| 1 | 0 | 0 |
| 3 | 1 | 0 |
| 5 | NULL | 0 |
+---+------+---+
Use the IS NULL
or IS NOT NULL
clauses when checking for NULL
values.
> SELECT * FROM t1 WHERE b IS NULL AND c IS NOT NULL;
+---+------+---+
| a | b | c |
+---+------+---+
| 5 | NULL | 0 |
| 6 | NULL | 1 |
+---+------+---+
NULLs and conditional operators
The conditional
operators
(including IF
, COALESCE
, IFNULL
) only evaluate some
operands depending on the value of a condition operand, so their
result is not always NULL
depending on the given operands.
For example, COALESCE(1, NULL)
will always return 1
even though
the second operand is NULL
.
NULLs and ternary logic
AND
, OR
and IS
implement ternary logic, as follows.
Expression | Result |
---|---|
FALSE AND FALSE |
FALSE |
FALSE AND TRUE |
FALSE |
FALSE AND NULL |
FALSE |
TRUE AND FALSE |
FALSE |
TRUE AND TRUE |
TRUE |
TRUE AND NULL |
NULL |
NULL AND FALSE |
FALSE |
NULL AND TRUE |
NULL |
NULL AND NULL |
NULL |
Expression | Result |
---|---|
FALSE OR FALSE |
FALSE |
FALSE OR TRUE |
TRUE |
FALSE OR NULL |
NULL |
TRUE OR FALSE |
TRUE |
TRUE OR TRUE |
TRUE |
TRUE OR NULL |
TRUE |
NULL OR FALSE |
NULL |
NULL OR TRUE |
TRUE |
NULL OR NULL |
NULL |
Expression | Result |
---|---|
FALSE IS FALSE |
TRUE |
FALSE IS TRUE |
FALSE |
FALSE IS NULL |
FALSE |
TRUE IS FALSE |
FALSE |
TRUE IS TRUE |
TRUE |
TRUE IS NULL |
FALSE |
NULL IS FALSE |
FALSE |
NULL IS TRUE |
FALSE |
NULL IS NULL |
TRUE |
NULLs and arithmetic
Arithmetic operations involving a NULL
value will yield a NULL
result.
> SELECT a, b, c, b*0, b*c, b+c FROM t1;
+---+------+------+-------+-------+-------+
| a | b | c | b * 0 | b * c | b + c |
+---+------+------+-------+-------+-------+
| 1 | 0 | 0 | 0 | 0 | 0 |
| 2 | 0 | 1 | 0 | 0 | 1 |
| 3 | 1 | 0 | 0 | 0 | 1 |
| 4 | 1 | 1 | 0 | 1 | 2 |
| 5 | NULL | 0 | NULL | NULL | NULL |
| 6 | NULL | 1 | NULL | NULL | NULL |
| 7 | NULL | NULL | NULL | NULL | NULL |
+---+------+------+-------+-------+-------+
NULLs and aggregate functions
Aggregate functions are those that operate on a set of rows and return a single value. The example data has been repeated here to make it easier to understand the results.
> SELECT * FROM t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 0 | 0 |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
| 5 | NULL | 0 |
| 6 | NULL | 1 |
| 7 | NULL | NULL |
+---+------+------+
> SELECT COUNT(*), COUNT(b), SUM(b), AVG(b), MIN(b), MAX(b) FROM t1;
+----------+----------+--------+--------------------+--------+--------+
| COUNT(*) | COUNT(b) | SUM(b) | AVG(b) | MIN(b) | MAX(b) |
+----------+----------+--------+--------------------+--------+--------+
| 7 | 4 | 2 | 0.5000000000000000 | 0 | 1 |
+----------+----------+--------+--------------------+--------+--------+
Note the following:
NULL
values are not included in theCOUNT()
of a column.COUNT(*)
returns 7 whileCOUNT(b)
returns 4.NULL
values are not considered as high or low values inMIN()
orMAX()
.AVG(b)
returnsSUM(b)/COUNT(b)
, which is different thanAVG(*)
asNULL
values are not considered in theCOUNT(b)
of rows. See NULLs as Other Values for more details.
NULL as a distinct value
NULL
values are considered distinct from other values and are included in the list of distinct values from a column.
> SELECT DISTINCT b FROM t1;
+------+
| b |
+------+
| 0 |
| 1 |
| NULL |
+------+
However, counting the number of distinct values excludes NULL
s, which is consistent with the COUNT()
function.
> SELECT COUNT(DISTINCT b) FROM t1;
+-------------------+
| count(DISTINCT b) |
+-------------------+
| 2 |
+-------------------+
NULLs as other values
In some cases, you may want to include NULL
values in arithmetic or aggregate function calculations. To do so, use the IFNULL()
function to substitute a value for NULL
during calculations.
For example, let's say you want to calculate the average value of column b
as being the SUM()
of all numbers in b
divided by the total number of rows, regardless of whether b
's value is NULL
. In this case, you would use AVG(IFNULL(b, 0))
, where IFNULL(b, 0)
substitutes a value of zero (0) for NULL
s during the calculation.
> SELECT COUNT(*), COUNT(b), SUM(b), AVG(b), AVG(IFNULL(b, 0)), MIN(b), MAX(b) FROM t1;
+----------+----------+--------+--------------------+--------------------+--------+--------+
| COUNT(*) | COUNT(b) | SUM(b) | AVG(b) | AVG(IFNULL(b, 0)) | MIN(b) | MAX(b) |
+----------+----------+--------+--------------------+--------------------+--------+--------+
| 7 | 4 | 2 | 0.5000000000000000 | 0.2857142857142857 | 0 | 1 |
+----------+----------+--------+--------------------+--------------------+--------+--------+
NULLs and set operations
NULL
values are considered as part of a UNION
set operation.
> SELECT b FROM t1 UNION SELECT b FROM t1;
+------+
| b |
+------+
| 0 |
| 1 |
| NULL |
+------+
NULLs and sorting
When sorting a column containing NULL
values, CockroachDB sorts NULL
values first with ASC
and last with DESC
. This differs from PostgreSQL, which sorts NULL
values last with ASC
and first with DESC
.
Use the NULLS FIRST
and NULLS LAST
options of the ORDER BY
to change where NULL
values appear in the sort order.
> SELECT * FROM t1 ORDER BY b ASC;
+---+------+------+
| a | b | c |
+---+------+------+
| 6 | NULL | 1 |
| 5 | NULL | 0 |
| 7 | NULL | NULL |
| 1 | 0 | 0 |
| 2 | 0 | 1 |
| 4 | 1 | 1 |
| 3 | 1 | 0 |
+---+------+------+
> SELECT * FROM t1 ORDER BY b DESC;
+---+------+------+
| a | b | c |
+---+------+------+
| 4 | 1 | 1 |
| 3 | 1 | 0 |
| 2 | 0 | 1 |
| 1 | 0 | 0 |
| 7 | NULL | NULL |
| 6 | NULL | 1 |
| 5 | NULL | 0 |
+---+------+------+
NULLs and unique constraints
NULL
values are not considered unique. Therefore, if a table has a UNIQUE
constraint on one or more columns that are optional (nullable), it is possible to insert multiple rows with NULL
values in those columns, as shown in the example below.
> CREATE TABLE t2(a INT, b INT UNIQUE);
> INSERT INTO t2 VALUES(1, 1);
> INSERT INTO t2 VALUES(2, NULL);
> INSERT INTO t2 VALUES(3, NULL);
> SELECT * FROM t2;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
| 2 | NULL |
| 3 | NULL |
+---+------+
NULLs and CHECK Constraints
A CHECK
constraint expression that evaluates to NULL
is considered to pass, allowing for concise expressions like discount < price
without worrying about adding OR discount IS NULL
clauses. When non-null validation is desired, the usual NOT NULL
constraint can be used alongside a CHECK
constraint.
> CREATE TABLE products (id STRING PRIMARY KEY, price INT NOT NULL CHECK (price > 0), discount INT, CHECK (discount <= price));
> INSERT INTO products (id, price) VALUES ('ncc-1701-d', 100);
> INSERT INTO products (id, price, discount) VALUES ('ncc-1701-a', 100, 50);
> SELECT * FROM products;
+----------+-------+----------+
| id | price | discount |
+----------+-------+----------+
| ncc1701a | 100 | 50 |
| ncc1701d | 100 | NULL |
+----------+-------+----------+
> INSERT INTO products (id, price) VALUES ('ncc-1701-b', -5);
failed to satisfy CHECK constraint (price > 0)
> INSERT INTO products (id, price, discount) VALUES ('ncc-1701-b', 100, 150);
failed to satisfy CHECK constraint (discount <= price)
NULLs and concatenation with other types
Concatenation between a non-NULL
value and a NULL
value results in a NULL
value.
In CockroachDB v20.2 and earlier, for all values other than STRING
, concatenation between a non-NULL
value and a NULL
value results in an ARRAY
of the non-NULL
value's type. To return an ARRAY
of a specific type from a NULL
concatenation in CockroachDB v21.1 and later, cast the NULL
value to an ARRAY
.
For example:
> SELECT NULL || 1;
?column?
------------
NULL
(1 row)
> SELECT NULL || `item`;
?column?
------------
NULL
(1 row)