SQL NULL: find missing matches and filter safely

Who this is for: learners who know the basics of SELECT and joins. The examples build on the SQL SELECT and WHERE lesson and the INNER JOIN and LEFT JOIN lesson.

What you will learn: what NULL means in a query result, how to test for it with IS NULL, and how a filter on the right-hand table can remove unmatched rows from a left join.

Use the shared sample data

These are the same customers and orders used in the other SQL lessons. The schema deliberately requires an order amount, so the NULLs in the join results below are generated for a customer with no matching order; they are not stored order amounts.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(8, 2) NOT NULL
);

INSERT INTO customers (customer_id, customer_name) VALUES
    (1, 'Asha'),
    (2, 'Ben'),
    (3, 'Chloe');

INSERT INTO orders (order_id, customer_id, amount) VALUES
    (101, 1, 120.00),
    (102, 1, 80.00),
    (103, 3, 200.00);

The examples use standard LEFT JOIN and IS NULL syntax supported by MySQL and PostgreSQL. The queries are not tested against every database version.

What does NULL mean?

NULL represents a missing, unknown, or inapplicable value. It is not the same as zero, an empty string, or the word 'NULL'. In a LEFT JOIN, SQL produces NULLs for columns on the right side when no matching row exists.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Test for NULL with IS NULL

To find customers who have no orders, check a right-side column that cannot be NULL for an actual order, such as its primary key:

SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Use IS NOT NULL to find values that are present. Do not write o.order_id = NULL or o.order_id <> NULL: comparisons with NULL produce an unknown result, not true or false. A WHERE clause keeps only rows for which its condition is true, so those comparisons do not find the missing values.

Why a WHERE filter can undo a LEFT JOIN

This query keeps only joined rows whose order amount is at least 100. Ben’s generated NULL does not satisfy the comparison, so his row is removed. Asha’s 80 order is removed too:

SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.amount >= 100
ORDER BY c.customer_id, o.order_id;

Keep every customer and filter matching orders

If you want all customers but only want matching orders of at least 100, put the order condition in the ON clause. Customers with no qualifying order remain, with NULLs in the order columns:

SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
    AND o.amount >= 100
ORDER BY c.customer_id, o.order_id;

Try it yourself

Write a query that lists every customer and shows only orders below 100, while keeping customers without a qualifying order. The result should contain Asha with order 102, Ben with NULL order details, and Chloe with NULL order details.

SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
    AND o.amount < 100
ORDER BY c.customer_id, o.order_id;

Key idea

Use IS NULL and IS NOT NULL to test missing values. With a LEFT JOIN, decide whether a right-side condition should filter the final rows (WHERE) or only which rows are allowed to match (ON).