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:
NULLvalues are not included in theCOUNT()of a column.COUNT(*)returns 7 whileCOUNT(b)returns 4.NULLvalues are not considered as high or low values inMIN()orMAX().AVG(b)returnsSUM(b)/COUNT(b), which is different thanAVG(*)asNULLvalues 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 NULLs, 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 NULLs 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 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)
