Example 1
Keep every customer while only matching paid orders
Alice has a paid order, so her amount matches normally. Bob has an order, but it is not paid, so the ON filter prevents that row from matching and leaves paid_total as NULL. Carol has no orders at all and is still preserved. If o.status = 'paid' were moved to WHERE, Bob and Carol would disappear.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE customers (id INT, name VARCHAR(50));
CREATE TABLE orders (
id INT,
customer_id INT,
status VARCHAR(20),
total INT
);
INSERT INTO
customers (id, name)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol');
INSERT INTO
orders (id, customer_id, status, total)
VALUES
(1, 1, 'paid', 100),
(2, 2, 'pending', 75);Validated query Shared across supported engines.
SQL
SELECT
c.name,
o.total AS paid_total
FROM
customers c
LEFT JOIN orders o ON c.id = o.customer_id
AND o.status = 'paid'
ORDER BY
c.id;Expected result Returned rows for the shared example.
| name | paid_total |
|---|---|
| Alice | 100 |
| Bob | NULL |
| Carol | NULL |
The key behavior is that Bob and Carol are preserved even though they have no paid match.