The relational mental model — sets, grain, and NULL

At a glance

Learning objectives

• State the grain of a result set in one sentence
• Explain how a join's cardinality (1:1, N:1, N:N) changes the row count of the result
• Name the five correctness failure modes the SQL review rules target

What you need

• No engine required — this is a concepts lesson
• [object Object]
• A query from your own team to state the grain of

Steps

1. Start every query with the review skill's first question: what is the grain of this result? If the author can't answer, stop. (from skill-sql-review )
2. State the grain of a result set in one sentence. (from skill-sql-review )
3. A query returns a set — and every join states its cardinality (1:1, N:1, N:N) and justifies it, because cardinality is what changes the grain. (from skill-sql-review )
4. NULL is part of the model and it never compares as you think — learn to spot NULL-unsafe `NOT IN` and `=` predicates. (from skill-sql-review )
5. The failure modes the series attacks — fan-out joins, NULL-unsafe predicates, implicit casts, non-deterministic ordering, and functions that silently change grain. (from skill-sql-review )
6. The artifact of the mental model is a grain statement for each SELECT. (from agent-sql-reviewer )

Check yourself

• You can answer the first question — the grain of a given result set — in one sentence.
• Given a join, you can say whether it is 1:1, N:1, or N:N and what that does to the row count.
• You can explain why NOT IN over a nullable subquery column is banned.

Worked example

The first question: what is the grain of this result?
If the author can't answer, stop.

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 Review
• SQL Reviewer

Security scan

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

Common gotchas

• NOT IN with a nullable column never compares as you think; use NOT EXISTS.
• A result set can be technically correct and still wrong — if the grain is wrong, the numbers are wrong.
• Grain is a property of the result, not of the tables — the same join can be N:1 or N:N depending on the keys.

Production considerations

• Make a grain statement part of every SQL PR description.
• The five review rules come from the sql-review skill — keep them next to the mental model.

Diagram

relational mental model
   |
   |- query = set --> state the grain in one sentence
   |
   |- join cardinality (1:1, N:1, N:N) --> what happens to the row count
   |
   |- NULL --> never compares as you think --> NOT EXISTS over NOT IN
   |
   |- failure modes --> fan-out, NULL-unsafe, casts, ordering, grain-changing functions

Registry stats