SQL IS NULL

What is the SQL IS NULL operator?

IS NULL tests whether a column or expression contains the NULL marker (unknown or missing value).

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL IS NULL: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2

SQL IS NULL Full Explanation

IS NULL is a unary logical operator used in WHERE, HAVING, JOIN, and CASE clauses to filter rows whose value is NULL. NULL represents an unknown or missing value and is never equal to anything, not even another NULL. Because of this tri-valued logic, normal equality comparisons (="" or <>" ") cannot detect NULLs. IS NULL returns TRUE when the operand is NULL, otherwise FALSE. If the operand is the result of a subquery, expression, or aggregate, the same rule applies. IS NULL never returns NULL; it resolves to a boolean that the query engine can use for filtering.Caveats:- NULLs propagate through most expressions, so calculations like price * quantity may return NULL and require IS NULL checks.- Some dialects support optimized null-aware join syntax, but basic IS NULL works everywhere.- IS NULL differs from empty string or zero. In most databases, '' and 0 are actual values, not NULL.

SQL IS NULL Syntax

-- In a SELECT filter
SELECT *
FROM   table_name
WHERE  column_name IS NULL;

-- In a CASE
CASE WHEN expression IS NULL THEN 'missing' ELSE 'present' END

-- With HAVING
HAVING COUNT(*) FILTER (WHERE col IS NULL) > 0;

SQL IS NULL Parameters

Example Queries Using SQL IS NULL

-- 1. Find users without an email address
SELECT user_id, name
FROM   users
WHERE  email IS NULL;

-- 2. Left join keeping unmatched rows
SELECT o.order_id, c.customer_id
FROM   orders o
LEFT  JOIN customers c ON o.customer_id = c.customer_id
WHERE  c.customer_id IS NULL;

-- 3. Replace NULL with default in result
SELECT COALESCE(discount, 0) AS discount_applied
FROM   sales
WHERE  discount IS NULL;

-- 4. Count nulls per column
SELECT SUM(CASE WHEN last_login IS NULL THEN 1 ELSE 0 END) AS null_logins
FROM   accounts;

Expected Output Using SQL IS NULL

Returns only rows where the email column has no value.
Returns orders that have no matching customer record.
Shows rows whose discount was NULL, emitting 0 in output.
Produces an integer count of NULL occurrences.

Use Cases with SQL IS NULL

Identify incomplete records before data migration
Detect orphaned rows after a LEFT JOIN
Build data quality dashboards (count of NULLs)
Drive conditional logic in CASE expressions
Replace or impute missing values via COALESCE

Common Mistakes with SQL IS NULL

Using = NULL or <> NULL instead of IS NULL / IS NOT NULL
Confusing NULL with empty string or zero
Forgetting that arithmetic with NULL yields NULL, hiding issues
Assuming two NULLs compare equal in GROUP BY without special handling

First Introduced In

SQL-92 Standard

Frequently Asked Questions

What is the difference between IS NULL and IS NOT NULL?

IS NULL matches rows where the value is NULL. IS NOT NULL matches rows where the value is anything other than NULL.

Can I index NULL values for faster IS NULL queries?

Most databases store NULLs inside indexes. Creating an index on the column usually speeds up IS NULL filters, but check your dialect's rules.

How do I count NULLs in a column?

Use COUNT(*) with a CASE or COUNT(column) = total - COUNT(column) since COUNT(column) ignores NULLs.

Does GROUP BY treat NULLs as equal?

Yes. In grouping operations, all NULLs are placed in the same group, even though NULL = NULL is not true in comparisons.