Review SQL for fan-out joins

At a glance

Learning objectives

• Prove a join cannot fan out before writing it
• Spot NULL-unsafe `NOT IN` and `=` predicates
• State the grain of a result set in one sentence

What you need

• A pull request or a .sql file
• The target dialect (postgres/duckdb/snowflake)
• [object Object]

Steps

1. Read the diff and the file context. (from agent-sql-reviewer )
2. Establish the grain of every result set. (from agent-sql-reviewer )
3. Apply the SQL review rules; stop at the first fan-out. (from agent-sql-reviewer )
4. Emit findings with file:line and a rewritten clause. (from agent-sql-reviewer )

Check yourself

• On a 12-case fixture of known-bad SQL, correctly identifies 11 correctness bugs and 0 false positives.
• Reviewing a PR that joins orders to a non-unique customer snapshot — flags fan-out and proposes a dedupe CTE.

Worked example

npx de-agent-platform add sql-review

1. Every join states its cardinality (1:1, N:1, N:N) and justifies it.
2. NOT IN (subquery) is banned where the subquery can return NULL — use NOT EXISTS.
3. Comparisons across types must be explicit casts, not implicit.
4. ORDER BY without LIMIT in a view is meaningless — drop it.
5. SELECT * in a persisted model freezes an unstable schema.

Sourced from

• SQL Reviewer
• SQL Review

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• Stop at the first fan-out — fixing five joins at once hides which fix worked.
• NOT IN with a nullable column never compares as you think; use NOT EXISTS.
• State the grain of every result set in one sentence before judging the query.

Production considerations

• Keep the 12-case evaluation fixture in CI so regressions resurface.
• Emit findings with file positions and a rewritten clause reviewers can verify.

Diagram

sql review pass
   |
   |- read diff + context --> state grain per result set
   |
   |- apply 5 rules --> first fan-out --> dedupe CTE
   |
   |- emit findings --> file:line + rewritten clause --> verify on fixture

Registry stats