Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT first_name FROM employees WHERE email IS NULL;

Try it

SQL Finding and excluding NULLs ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL NULL in expressions ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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)
SQL
-- Oracle: the empty string and NULL are the same thing
SELECT COUNT(*) FROM customers WHERE nickname IS NULL;  -- also counts ''

Usage notes

  • Never write = NULL or <> NULL. Both produce NULL for every row, so WHERE returns nothing. Use IS NULL / IS NOT NULL, or IS DISTINCT FROM when the other side may be NULL too.
  • 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-NULL emails while COUNT(*) counts rows. See COUNT().
  • Outer joins produce NULL for unmatched rows, so WHERE right_table.id IS NULL is the classic anti-join.
  • Indexes: most databases can use an index to find IS NULL rows. Oracle B-tree indexes skip entirely-NULL keys, so IS NULL on a single indexed column cannot use them.
  • Sorting: NULLs sort first in SQLite and MySQL ascending order, last in PostgreSQL and Oracle. Control it with NULLS FIRST / NULLS LAST where supported.
  • Replace NULL with a default using COALESCE() or IFNULL().