> ## 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;
```

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

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

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

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

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

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

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+---+------+---+
| a |  b   | c |
+---+------+---+
| 5 | NULL | 0 |
| 6 | NULL | 1 |
+---+------+---+
```

## NULLs and conditional operators

The <InternalLink path="scalar-expressions#conditional-expressions">conditional operators</InternalLink> (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;
```

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

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

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

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

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

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

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

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

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

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

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

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

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ?column?
------------
  NULL
(1 row)
```

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ?column?
------------
  NULL
(1 row)
```

## Known limitations

* By default, CockroachDB orders `NULL` s before all other values. For compatibility with PostgreSQL, the `null_ordered_last` <InternalLink path="set-vars">session variable</InternalLink> was added, which changes the default to order `NULL` values after all other values. This works in most cases, due to some transformations CockroachDB makes in the optimizer to add extra ordering columns. However, it does not work when the ordering column is a tuple.