IS NULL usage and examples

Overview

NULL represents missing or unknown information and cannot be tested with = NULL or <> NULL. IS NULL and IS NOT NULL explicitly identify missing or present values. An empty string, numeric zero, and NULL are different states, so define their business meaning before filtering, counting, or cleaning data.

Basic syntax

SELECT * FROM orders WHERE amount IS NOT NULL;

Examples

IS NULL example

Use IS NOT NULL to return orders whose amount is present instead of relying on an invalid equality comparison.

Sample table

Table：orders

orders
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Run result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Common issues

Are boundary values included?

Check the operator precisely: BETWEEN includes both ends, while adding or omitting equals changes a range.

How do I avoid mistakes with AND and OR?

Use parentheses to express business groups instead of relying on remembered precedence.

Why does a condition not match NULL?

NULL represents unknown, so a comparison may not be TRUE; use IS NULL or an explicit NULL strategy.

Keep reading

SELECT WHERE DISTINCT ORDER BY LIMIT LIKE BETWEEN OFFSET

IS NULL