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
.
CockroachDB supports NULLS FIRST
and NULLS LAST
in ORDER BY
clauses. However, in some cases the support is syntax-only—an error is returned if NULLS FIRST
or NULLS LAST
specification doesn't "match" the internal structure of the used index. If the index is ascending, the error is returned for NULLS LAST
; if the index is descending, the error is returned for NULLS FIRST
.
> 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 along side 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
New in v21.1:
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)