SQL JOIN & NULL filter explorer
Compare LEFT JOIN and INNER JOIN when filtering on a joined column in ON versus WHERE, and see how COUNT(*) differs from COUNT(column) on NULL-extended rows.
Free to use · No sign-up · Inputs stay in your browser
Move the filter. Watch the NULL rows.
Switch the filter between WHERE and ON, then compare COUNT(*) against COUNT(o.id).
Query under test
SELECT c.id, c.name, COUNT(o.id) AS matched_orders
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;Fixed input tables
customers: 1:Ada, 2:Linus, 3:Grace
orders: #101 (c=1, paid, $40) · #102 (c=1, refunded, $15) · #103 (c=2, refunded, $25)
Step-by-step evaluation
WHERE o.status = 'paid' runs after the LEFT JOIN. Because NULL = 'paid' is unknown, Linus and Grace are removed before GROUP BY — turning the LEFT JOIN into an INNER JOIN.
| Customer | o.id | o.status | o.amount | WHERE check |
|---|---|---|---|---|
| Ada | 101 | 'paid' | 40 | Kept |
| Ada | 102 | 'refunded' | 15 | Dropped |
| Linus | 103 | 'refunded' | 25 | Dropped |
| Grace | NULL | NULL | NULL | Dropped |
| c.name | COUNT(o.id) | COUNT(*) | COUNT(o.id) |
|---|---|---|---|
| Ada | 1 | 1 | 1 |
Omitted from output: Linus, Grace
Practice auditing a query against zero-row groups
A report must list every customer, including those with zero paid orders. Why does LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid' fail that contract?
Check the reasoning
For a customer with no paid orders, the LEFT JOIN produces NULL in o.status. The WHERE clause then evaluates NULL = 'paid', which is not true, so the customer row is dropped before GROUP BY. Move o.status = 'paid' into the ON clause and aggregate with COUNT(o.id).
This is an original open example, not a scored drill. Close the tool before taking a diagnostic or an unassisted practice session.
About 14 minutes, no account needed. A free account adds a daily practice queue; paid plans add more practice and the full library.
How to use the result
Choose a join type, move the status filter between WHERE and ON, and switch the aggregate between COUNT(o.id), COUNT(*) and COALESCE(SUM(o.amount), 0). The explorer traces the fixed three-customer fixture step by step in your browser.
Placing o.status = 'paid' inside the ON clause keeps customers with zero paid orders as NULL-extended rows, and COUNT(o.id) returns 0 for them. Moving that same condition to WHERE removes those NULL-extended rows before grouping.
These utilities and open examples do not contribute to your diagnostic score or practice history.
PostgreSQL: table expressions and JOIN clauses