SQL IS NOT NULL

What is SQL IS NOT NULL?

IS NOT NULL returns TRUE when the compared expression contains any non-NULL 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 NOT NULL: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2

SQL IS NOT NULL Full Explanation

IS NOT NULL is a unary predicate that evaluates whether an expression holds a definite value (i.e., is not the special NULL marker). Because NULL represents unknown or missing information, comparisons using the usual operators (=, <, >, etc.) treat NULL as unknown and return NULL. IS NOT NULL bypasses three-valued logic by explicitly checking for non-NULL, producing a Boolean TRUE or FALSE result. It can appear in SELECT, UPDATE, DELETE, CHECK constraints, JOIN conditions, and CASE expressions. The predicate never matches NULLs, even if they result from calculations, subqueries, or outer joins. It is sargable in most engines, allowing index usage when the underlying column is indexed and defined as NOT NULL. Caveats: data containing empty strings or zero dates are still considered NOT NULL; ANSI padding settings in some systems do not affect NULL evaluation; IS NOT NULL is not a function and therefore takes no parentheses.

SQL IS NOT NULL Syntax

expression IS NOT NULL

SQL IS NOT NULL Parameters

expression (any valid SQL expression or column. Data type) - arbitrary.

Example Queries Using SQL IS NOT NULL

-- Filter rows where email exists
SELECT id, email
FROM users
WHERE email IS NOT NULL;

-- Update only populated phone numbers
UPDATE customers
SET verified_phone = TRUE
WHERE phone IS NOT NULL;

-- Delete orphaned orders whose customer_id is NULL
DELETE FROM orders
WHERE customer_id IS NOT NULL;

-- CASE example
SELECT order_id,
       CASE WHEN shipped_at IS NOT NULL THEN 'Shipped' ELSE 'Pending' END AS status
FROM orders;

Expected Output Using SQL IS NOT NULL

Each statement returns, updates, or deletes only the rows whose evaluated expression is non-NULL
NULL values are excluded

Use Cases with SQL IS NOT NULL

Filtering out incomplete records before analytics
Enforcing business rules in CHECK constraints (e.g., either email IS NOT NULL OR phone IS NOT NULL)
Preventing updates to rows lacking required data
Distinguishing populated versus missing timestamps in status columns
Joining only rows that have keys present (INNER JOIN ... ON t1.fk = t2.id AND t1.fk IS NOT NULL)

Common Mistakes with SQL IS NOT NULL

Assuming = NULL or <> NULL works; only IS (NOT) NULL does
Confusing empty strings ('') with NULL
Forgetting calculated columns can return NULL and need IS NOT NULL checks
Using parentheses after the predicate, e.g., column IS NOT NULL()

First Introduced In

SQL-92

Frequently Asked Questions

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

= NULL returns UNKNOWN because NULL is not equal to anything, including itself. IS NOT NULL explicitly checks for the absence of NULL and returns TRUE.

Does IS NOT NULL work on computed columns?

Yes. If the expression evaluates to a non-NULL value, IS NOT NULL is TRUE; if it evaluates to NULL, it is FALSE.

How do I include NULLs instead of excluding them?

Use IS NULL to target rows where the expression is NULL. Combine predicates with OR to retrieve both if needed.

Will IS NOT NULL slow down my query?

On indexed, NOT NULL columns most engines can satisfy the predicate using the index. On NULL-able columns, a full scan might be required.