Workspace debugging guide
How do you review SQL JOIN, GROUP BY, and NULL behavior safely?
Published
Published by Unrust. Scoring and editorial method
Quick 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.
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.
| Observed condition | Review decision | What to verify |
|---|---|---|
| A query uses LEFT JOIN to keep parent rows with zero child rows, and also filters on a child-table column | Check whether the child-column filter is placed in WHERE or inside the LEFT JOIN ... ON clause | 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 | Check whether COUNT(*) vs COUNT(child.id) is used and how COALESCE wraps SUM(child.amount) | 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 | Trace a parent row that has 2 rows in child table A and 3 rows in child table B | Verify that pre-aggregated subqueries or CTEs prevent a 2×3 Cartesian fan-out from inflating SUM or COUNT |
Interactive reasoning check
Try the SQL review: what does the query return for a customer with no orders?
Read the reasoning without answering
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.
A small practice loop
- 01Read every SQL query in logical evaluation order: FROM/JOIN → WHERE → GROUP BY → Aggregates → SELECT.
- 02Trace one parent row with zero child rows and one parent row with two child rows through the query by hand.
- 03Use 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.
Limits of this guide
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.
Sources and scope
Original editorial guide by the Unrust team covering three-valued SQL logic, outer-join semantics, and aggregate verification.
- PostgreSQL Documentation: Table Expressions — JOIN, WHERE, and GROUP BY Processing
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)
PostgreSQL Global Development Group
Documents the exact distinction between COUNT(*) and COUNT(expression) when grouped rows contain NULL values.
Check your own starting point
Unrust uses short, timed, unassisted drills to give developers a per-skill snapshot. One session cannot explain the cause of a result, but it can show you what is worth practicing next.
Take the free diagnostic