# 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.

- Canonical HTML: [https://unrust.dev/tools/sql-join-null-explorer](https://unrust.dev/tools/sql-join-null-explorer)
- Markdown representation: [https://unrust.dev/tools/sql-join-null-explorer/index.md](https://unrust.dev/tools/sql-join-null-explorer/index.md)

## How to use the tool

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.

## Worked example

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.

```text
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name;
```

Inputs stay in your browser. These are open learning examples, not scored diagnostic drills.

[Why a WHERE filter turns a LEFT JOIN into an INNER JOIN](https://unrust.dev/blog/sql-left-join-where-null-filter/index.md)

[PostgreSQL: table expressions and JOIN clauses](https://www.postgresql.org/docs/current/queries-table-expressions.html)

## 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?

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).

Original open example, not a scored drill. Close tools before an unassisted session.

[Practice tracing execution step by step](https://unrust.dev/guides/code-tracing/index.md)

[Take the free diagnostic](https://unrust.dev/diagnostic/index.md)