# How do you review SQL JOIN, GROUP BY, and NULL behavior safely? > Learn how to review SQL queries by tracing intermediate row sets through JOIN, WHERE, GROUP BY, and NULL-sensitive aggregates before checking the final SELECT. - Canonical HTML: [https://unrust.dev/guides/debugging-sql-joins-and-null-aggregates](https://unrust.dev/guides/debugging-sql-joins-and-null-aggregates) - Markdown representation: [https://unrust.dev/guides/debugging-sql-joins-and-null-aggregates/index.md](https://unrust.dev/guides/debugging-sql-joins-and-null-aggregates/index.md) Published: 2026-10-22 Published by Unrust. Scoring and editorial method: https://unrust.dev/help#methodology ## Direct answer To review SQL JOIN, GROUP BY, and NULL behavior safely, trace a tiny 3-to-4-row dataset through the query in logical execution order—FROM and JOIN first, then WHERE, then GROUP BY and aggregate functions, and finally SELECT—making sure your test rows include a parent row with zero matching child rows, a parent row with multiple child rows, and a row containing NULL in the filtered or aggregated column. ## Trace SQL in logical execution order, not written order SQL is written starting with SELECT, which tempts reviewers to read the column list first and assume the rest of the query simply fetches those fields. At runtime, the database evaluates FROM and JOIN conditions first, filters the joined rows in WHERE, collapses groups in GROUP BY, computes aggregates like COUNT or SUM, and projects SELECT last. Tracing in logical order makes row-multiplication and row-elimination bugs immediately visible. - [Try the interactive SQL JOIN & NULL explorer](https://unrust.dev/tools/sql-join-null-explorer/index.md) - [Read the worked walkthrough on LEFT JOIN and WHERE NULL filters](https://unrust.dev/blog/sql-left-join-where-null-filter/index.md) ## Check the three classic SQL review traps First, a WHERE predicate on a right-table column (such as WHERE o.status = 'paid') rejects NULL-extended rows and silently turns a LEFT JOIN into an INNER JOIN unless the predicate lives in the ON clause or explicitly handles NULL. Second, COUNT(*) counts rows in the group (returning 1 for an unmatched LEFT JOIN row), whereas COUNT(o.id) counts non-NULL values (returning 0). Third, joining a parent table to two one-to-many child tables in the same query multiplies rows before SUM or COUNT runs. ## Always test with a 3-row mental fixture You do not need a database engine to catch most query logic bugs in code review. Construct three mental parent rows: one with zero child matches, one with a child row whose status or value is NULL or filtered out, and one with two matching child rows. Walk those three rows from JOIN to WHERE to GROUP BY, and compare the resulting numbers against what the caller or dashboard expects. ## A SQL query review decision table Use this checklist whenever a pull request adds or modifies a reporting, billing, or analytics SQL query. ### A query uses LEFT JOIN to keep parent rows with zero child rows, and also filters on a child-table column - **Review decision:** Check whether the child-column filter is placed in WHERE or inside the LEFT JOIN ... ON clause - **Evidence to verify:** Verify with a zero-match parent row that NULL-extended child columns are not rejected by WHERE ### A grouped query reports per-parent totals using COUNT or SUM after a LEFT JOIN - **Review decision:** Check whether COUNT(*) vs COUNT(child.id) is used and how COALESCE wraps SUM(child.amount) - **Evidence to verify:** Confirm that an unmatched parent row produces 0 rather than 1 (from COUNT(*)) or NULL (from un-coalesced SUM) ### A query joins a parent table to more than one one-to-many child table before aggregating - **Review decision:** Trace a parent row that has 2 rows in child table A and 3 rows in child table B - **Evidence to verify:** Verify that pre-aggregated subqueries or CTEs prevent a 2×3 Cartesian fan-out from inflating SUM or COUNT ## Try the SQL review: what does the query return for a customer with no orders? Consider the query: SELECT c.id, COUNT(*) AS order_count FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id; Customer 'cust_empty' has zero rows in the orders table. What order_count does this query return for 'cust_empty'? - 0, because there are no matching rows in the orders table. - 1, because LEFT JOIN produces one NULL-extended row for 'cust_empty' and COUNT(*) counts rows rather than non-NULL o.id values. - 'cust_empty' is omitted from the result set entirely. - NULL, because COUNT(*) returns NULL when a joined column is NULL. **Reasoning:** After a LEFT JOIN, an unmatched parent row still exists as one row in the grouped set with NULLs in all right-table columns. Use COUNT(o.id) when you want zero for unmatched parents. ## Practice loop 1. Read every SQL query in logical evaluation order: FROM/JOIN → WHERE → GROUP BY → Aggregates → SELECT. 2. Trace one parent row with zero child rows and one parent row with two child rows through the query by hand. 3. Use the interactive SQL JOIN & NULL Explorer at /tools/sql-join-null-explorer to compare LEFT JOIN ... ON filters, WHERE filters, and COUNT(col) vs COUNT(*). ## Common questions ### Where does three-valued logic (TRUE, FALSE, UNKNOWN) trip up SQL code reviews most often? In WHERE and NOT IN clauses: any comparison like o.status = 'paid' or o.status != 'cancelled' evaluates to UNKNOWN when o.status is NULL, and WHERE keeps only rows where the predicate is TRUE. ### Can I test SQL JOIN and NULL behavior interactively on Unrust? Yes. Open the free SQL JOIN & NULL Explorer at /tools/sql-join-null-explorer to step through ON vs WHERE filters and aggregate behavior on a sample schema. ## Limitations Logical row-set tracing verifies query semantics on small fixtures; it does not evaluate index selection, query-planner cardinality estimates, or lock contention on large production tables. ## Provenance Original editorial guide by the Unrust team covering three-valued SQL logic, outer-join semantics, and aggregate verification. ## Sources - [PostgreSQL Documentation: Table Expressions — JOIN, WHERE, and GROUP BY Processing](https://www.postgresql.org/docs/current/queries-table-expressions.html) — PostgreSQL Global Development Group. Specifies the logical evaluation sequence from JOIN construction through WHERE filtering and GROUP BY aggregation. - [PostgreSQL Documentation: Aggregate Functions (COUNT, SUM, and NULL handling)](https://www.postgresql.org/docs/current/functions-aggregate.html) — PostgreSQL Global Development Group. Documents the exact distinction between COUNT(*) and COUNT(expression) when grouped rows contain NULL values. ## Related guides - [How do you read code against its contract?](https://unrust.dev/guides/reading-code-against-a-contract/index.md): Learn how to compare implementation with intent, find mismatched assumptions, and catch bugs that clean-looking code can hide. - [What is code tracing?](https://unrust.dev/guides/code-tracing/index.md): Learn code tracing with a worked JavaScript loop, step-by-step state tables, an array-alias example, and a free reasoning check. - [How do you trace a bug across multiple files without an IDE?](https://unrust.dev/guides/debugging-across-multiple-files/index.md): Learn a structured method for tracing contracts, data shapes, and state mutations across module boundaries when a bug spans multiple files. Next step: [Take the free diagnostic](https://unrust.dev/diagnostic/index.md)