> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# NULL Handling

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

`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 <InternalLink path="cockroach-sql">built-in SQL client</InternalLink>.

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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO customers (customer_id, cust_name, cust_email) VALUES (1, 'Smith', NULL);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE t1(
  a INT,
  b INT,
  c INT
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t1 VALUES(1, 0, 0);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t1 VALUES(2, 0, 1);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t1 VALUES(3, 1, 0);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t1 VALUES(4, 1, 1);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t1 VALUES(5, NULL, 0);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t1 VALUES(6, NULL, 1);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t1 VALUES(7, NULL, NULL);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 |
+---+------+------+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t1 WHERE b < 10;
```

```
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 0 | 0 |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
+---+---+---+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t1 WHERE NOT b > 10;
```

```
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 0 | 0 |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
+---+---+---+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 |
+---+------+---+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t1 WHERE b < 10 AND c = 1;
```

```
+---+---+---+
| a | b | c |
+---+---+---+
| 2 | 0 | 1 |
| 4 | 1 | 1 |
+---+---+---+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t1 WHERE NOT (b < 10 AND c = 1);
```

```
+---+------+---+
| a |  b   | c |
+---+------+---+
| 1 |    0 | 0 |
| 3 |    1 | 0 |
| 5 | NULL | 0 |
+---+------+---+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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](scalar-expressions.html#conditional-expressions)
(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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 <InternalLink path="functions-and-operators">functions</InternalLink> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 |
+---+------+------+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 the `COUNT()` of a column. `COUNT(*)` returns 7 while `COUNT(b)` returns 4.

* `NULL` values are not considered as high or low values in `MIN()` or `MAX()`.

* `AVG(b)` returns `SUM(b)/COUNT(b)`, which is different than `AVG(*)` as `NULL` values are not considered in the `COUNT(b)` of rows. See [NULLs as Other Values](#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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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` <InternalLink path="selection-queries#set-operations">set operation</InternalLink>.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT b FROM t1 UNION SELECT b FROM t1;
```

```
+------+
|  b   |
+------+
|    0 |
|    1 |
| NULL |
+------+
```

## NULLs and sorting

When <InternalLink path="order-by">sorting a column</InternalLink> 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 <InternalLink path="order-by#parameters">`ORDER BY`</InternalLink> to change where `NULL` values appear in the sort order.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 |
+---+------+------+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE t2(a INT, b INT UNIQUE);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t2 VALUES(1, 1);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t2 VALUES(2, NULL);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t2 VALUES(3, NULL);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t2;
```

```
+---+------+
| a |  b   |
+---+------+
| 1 |    1 |
| 2 | NULL |
| 3 | NULL |
+---+------+
```

## NULLs and CHECK Constraints

A <InternalLink path="check">`CHECK` constraint</InternalLink> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE products (id STRING PRIMARY KEY, price INT NOT NULL CHECK (price > 0), discount INT, CHECK (discount <= price));
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO products (id, price) VALUES ('ncc-1701-d', 100);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO products (id, price, discount) VALUES ('ncc-1701-a', 100, 50);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM products;
```

```
+----------+-------+----------+
|    id    | price | discount |
+----------+-------+----------+
| ncc1701a |   100 |       50 |
| ncc1701d |   100 | NULL     |
+----------+-------+----------+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO products (id, price) VALUES ('ncc-1701-b', -5);
```

```
failed to satisfy CHECK constraint (price > 0)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

<Note>
  In CockroachDB v20.2 and earlier, for all values other than <InternalLink path="string">`STRING`</InternalLink>, concatenation between a non-`NULL` value and a `NULL` value results in an <InternalLink path="array">`ARRAY`</InternalLink> 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, <InternalLink path="data-types#data-type-conversions-and-casts">cast</InternalLink> the `NULL` value to an `ARRAY`.
</Note>

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT NULL || 1;
```

```
  ?column?
------------
  NULL
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT NULL || `item`;
```

```
  ?column?
------------
  NULL
(1 row)
```