SQL Reference ยท Operators and Predicates
SQL IS NULL and IS NOT NULL
Reference for IS NULL and IS NOT NULL in SQL: the only correct way to test for missing values, why = NULL never works, and how it behaves in each database.
NULL marks a missing or unknown value. Because "unknown equals unknown" is itself unknown, column = NULL never evaluates to true. To test for NULL, SQL provides two dedicated predicates: IS NULL is true when the value is NULL, and IS NOT NULL is true when it is anything else.
Unlike most operators, these two never return NULL. They always give true or false, which makes them safe to combine with AND, OR and NOT.
SELECT first_name FROM employees WHERE email IS NULL;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
expression IS [NOT] NULL
| Part | Description |
|---|---|
expression |
A column, literal or any expression. |
IS NULL |
True when the expression is NULL. |
IS NOT NULL |
True when the expression is not NULL. Equivalent to NOT (expression IS NULL). |
Returns: a boolean, never NULL. SQLite and MySQL show it as 1 or 0.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
IS NULL, IS NOT NULL |
Yes | Yes | Yes | Yes | Yes |
Shorthand ISNULL / NOTNULL postfix |
Yes | Yes | No | No | No |
IS UNKNOWN for boolean values |
No | Yes | Yes | No | No |
Empty string is NULL |
No | No | No | No | Yes (for VARCHAR2) |
-- Oracle: the empty string and NULL are the same thing SELECT COUNT(*) FROM customers WHERE nickname IS NULL; -- also counts ''
Usage notes
- Never write
= NULLor<> NULL. Both produceNULLfor every row, soWHEREreturns nothing. UseIS NULL/IS NOT NULL, orIS DISTINCT FROMwhen the other side may beNULLtoo. - An empty string is not
NULL(except in Oracle). Check both when data is messy:WHERE email IS NULL OR email = ''. - Aggregates ignore
NULLs.COUNT(email)counts non-NULLemails whileCOUNT(*)counts rows. SeeCOUNT(). - Outer joins produce
NULLfor unmatched rows, soWHERE right_table.id IS NULLis the classic anti-join. - Indexes: most databases can use an index to find
IS NULLrows. Oracle B-tree indexes skip entirely-NULLkeys, soIS NULLon a single indexed column cannot use them. - Sorting:
NULLs sort first in SQLite and MySQL ascending order, last in PostgreSQL and Oracle. Control it withNULLS FIRST/NULLS LASTwhere supported. - Replace
NULLwith a default usingCOALESCE()orIFNULL().