IS NULL / IS NOT NULLQuery Techniques
Buy Me A Coffee

Test whether a value is NULL (missing/unknown). NULL cannot be compared with = or != — only IS NULL and IS NOT NULL work correctly.

The correct way to test for NULL values, since NULL = NULL evaluates to NULL (unknown) rather than TRUE. IS NOT NULL selects rows where a value is present. Always use this syntax -- never use equals NULL or not-equals NULL.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all IS NULL and IS NOT NULL standard. Also has the null-safe equality operator <=> (col <=> NULL is equivalent to col IS NULL), useful in joins where NULL should equal NULL.
PostgreSQL ✓ Supported all IS NULL and IS NOT NULL standard. IS DISTINCT FROM / IS NOT DISTINCT FROM are the null-safe equality operators: (a IS DISTINCT FROM b) is true when they differ even if one is NULL.
SQL Server ✓ Supported all IS NULL and IS NOT NULL standard. For null-safe comparison use SET ANSI_NULLS OFF (deprecated) or ISNULL()/COALESCE(). No dedicated null-safe equality operator.
Oracle ✓ Supported all IS NULL and IS NOT NULL standard. Critical Oracle quirk: empty string '' is treated as NULL — there is no distinction between empty string and NULL in Oracle.
SQLite ✓ Supported all IS NULL and IS NOT NULL standard. SQLite also supports IS as a null-safe equality operator: (a IS b) is true when both are NULL or both have the same value.

Details

Oracle's empty-string-as-NULL is one of the most surprising production gotchas in SQL — inserting '' into an Oracle VARCHAR2 stores NULL. Null-safe equality (where NULL equals NULL) has three different syntaxes: <=> in MySQL, IS NOT DISTINCT FROM in PostgreSQL, IS in SQLite, and no clean operator in SQL Server.

Standard Syntax

SELECT * FROM users WHERE phone IS NULL; SELECT * FROM users WHERE phone IS NOT NULL;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

IS NULL and IS NOT NULL standard. Also has the null-safe equality operator <=> (col <=> NULL is equivalent to col IS NULL), useful in joins where NULL should equal NULL.

SELECT * FROM users WHERE phone IS NULL; SELECT * FROM users WHERE phone IS NOT NULL; SELECT * FROM tbl WHERE col <=> NULL; -- null-safe equals SELECT * FROM tbl WHERE NOT (col <=> 'val'); -- null-safe not-equals

PostgreSQL

IS NULL and IS NOT NULL standard. IS DISTINCT FROM / IS NOT DISTINCT FROM are the null-safe equality operators: (a IS DISTINCT FROM b) is true when they differ even if one is NULL.

SELECT * FROM users WHERE phone IS NULL; SELECT * FROM users WHERE phone IS NOT NULL; SELECT * FROM tbl WHERE a IS NOT DISTINCT FROM b; -- null-safe equals SELECT * FROM tbl WHERE a IS DISTINCT FROM b; -- null-safe not-equals

SQL Server

IS NULL and IS NOT NULL standard. For null-safe comparison use SET ANSI_NULLS OFF (deprecated) or ISNULL()/COALESCE(). No dedicated null-safe equality operator.

SELECT * FROM users WHERE phone IS NULL; SELECT * FROM users WHERE phone IS NOT NULL; -- Null-safe equals workaround: SELECT * FROM tbl WHERE (a = b OR (a IS NULL AND b IS NULL));

Oracle

IS NULL and IS NOT NULL standard. Critical Oracle quirk: empty string '' is treated as NULL — there is no distinction between empty string and NULL in Oracle.

SELECT * FROM users WHERE phone IS NULL; -- In Oracle, these are equivalent: SELECT * FROM tbl WHERE col IS NULL; SELECT * FROM tbl WHERE col = ''; -- '' IS NULL in Oracle

SQLite

IS NULL and IS NOT NULL standard. SQLite also supports IS as a null-safe equality operator: (a IS b) is true when both are NULL or both have the same value.

SELECT * FROM users WHERE phone IS NULL; SELECT * FROM tbl WHERE a IS b; -- null-safe equals SELECT * FROM tbl WHERE a IS NOT b; -- null-safe not-equals