Categories:

Conditional Expression Functions

# IS [ NOT ] NULL¶

Determines whether an expression is NULL or is not NULL.

## Syntax¶

```<expr> IS [ NOT ] NULL
```

## Returns¶

Returns BOOLEAN true or false.

## Examples¶

```CREATE OR REPLACE TABLE i (id NUMBER, col1 NUMBER, col2 NUMBER);
INSERT INTO i (id, col1, col2) VALUES
(1, 0, 5),
(2, 0, null),
(3, null, 5),
(4, null, null);
```

Show the data:

```SELECT *
FROM i
ORDER BY id;
+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  1 |    0 |    5 |
|  2 |    0 | NULL |
|  3 | NULL |    5 |
|  4 | NULL | NULL |
+----+------+------+
```

Use `IS NOT NULL`:

```SELECT *
FROM i
WHERE col1 IS NOT NULL
ORDER BY id;
+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  1 |    0 |    5 |
|  2 |    0 | NULL |
+----+------+------+
```

Use `IS NULL`:

```SELECT *
FROM i
WHERE col2 IS NULL
ORDER BY id;
+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  2 |    0 | NULL |
|  4 | NULL | NULL |
+----+------+------+
```

Use a combination of `IS NOT NULL` and `IS NULL`:

```SELECT *
FROM i
WHERE col1 IS NOT NULL OR col2 IS NULL
ORDER BY id;
+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  1 |    0 |    5 |
|  2 |    0 | NULL |
|  4 | NULL | NULL |
+----+------+------+
```