SQL Conceptual

The reasoning

`x NOT IN (a, b, c)` is shorthand for `x <> a AND x <> b AND x <> c`. Comparing anything to NULL yields UNKNOWN rather than TRUE or FALSE, and `TRUE AND UNKNOWN` is UNKNOWN. So the moment one element of that list is NULL, the whole predicate can never be TRUE for any row — it is either FALSE (when x matches something) or UNKNOWN (when it does not). A WHERE clause keeps only rows where the predicate is TRUE, so the result is empty.

The asymmetry catches people: plain `IN` still works fine with a NULL present, because it only needs one comparison to be TRUE. It is specifically the negation that collapses. That is why the two look interchangeable in testing on clean data and diverge the first time the column is nullable.

`NOT EXISTS` is written as a correlated existence check rather than a chain of comparisons, so a NULL row in the subquery simply fails to match and is ignored. Anti-joining with `LEFT JOIN ... WHERE right.key IS NULL` behaves the same way. Both are NULL-safe.

The reasoning

`COUNT(*)` is the only one that counts rows as such — it never looks at a value, so it cannot be affected by NULLs. `COUNT(column)` evaluates the expression per row and skips NULLs, which makes it a completeness measure disguised as a count. `COUNT(DISTINCT column)` skips NULLs *and* collapses repeats.

That makes the three of them a free data-quality probe. Run all three over the same column and the differences are informative on their own: `COUNT(*) - COUNT(col)` is the number of missing values, and `COUNT(col) - COUNT(DISTINCT col)` is how much the column repeats. Engineers who know this reach for it before writing a single quality test.

The practical consequence is on joins. After a join has fanned out, `COUNT(*)` reports the inflated row count while `COUNT(DISTINCT order_id)` still reports the truth. If a report shows more orders than the source system, comparing those two numbers locates the problem in about ten seconds.

The reasoning

SQL genuinely has two rules. The `=` operator answers "are these equal", and with a NULL involved it cannot answer, so it returns UNKNOWN. Grouping asks a different question — "do these belong in the same bucket" — and for that the standard defines NULLs as *not distinct from* each other. The same rule governs `DISTINCT`, `UNION`, `INTERSECT` and `EXCEPT`.

So a `GROUP BY country` produces exactly one row for all the rows with a NULL country, and `SELECT DISTINCT country` returns NULL once. But `ON a.country = b.country` matches no NULL rows at all, because that is comparison. Both behaviours are correct and they are answering different questions.

The practical version: if you need NULLs to match in a join, you have to say so — `IS NOT DISTINCT FROM` where the dialect has it, or an explicit `a.k = b.k OR (a.k IS NULL AND b.k IS NULL)`. Doing it by coalescing to a sentinel value works until the sentinel appears in the data.

The reasoning

A good grain statement names the entity and every dimension that makes a row unique: not "one row per order" but "one row per order line, per warehouse, per day". If you cannot write that sentence, you do not yet know what a COUNT of the table means — and neither does anyone reading your report.

The grain is also the contract a join has to respect. Joining a table at order grain to one at order-line grain produces a result at line grain, which means every order-level measure now appears once per line. Nothing errors; the numbers just become wrong in proportion to the number of lines.

In an interview the strong move is to state the grain of the result you intend to produce before you write anything, and then to name what would break it. That single habit prevents most of the mistakes the rest of the interview is designed to find.

The reasoning

The join itself is behaving correctly — it is doing exactly what a join means. The problem is that the query then aggregates a column at the *old* grain over rows at the *new* grain. An order worth 100 that matches three line items contributes 300 to a naive SUM.

The tells are all count-based: the output has more rows than the driving table, `COUNT(*)` disagrees with `COUNT(DISTINCT order_id)`, and the total moves when you add a join that "only adds attributes". Any of those three is worth stopping for.

The fixes, in rough order of preference: aggregate the right side to the left side's grain before joining; or use a window function so nothing collapses; or `SUM(DISTINCT ...)` only when the values are provably unique, which they usually are not. The one that does not work is `SELECT DISTINCT` over the whole result — that removes duplicate rows, not duplicate contributions, and two orders that genuinely cost the same amount are then silently merged.

The reasoning

`NOT EXISTS` is the one to default to. It states the intent directly, it is NULL-safe, and every serious optimizer recognises it as an anti-join and executes it as one rather than as a per-row subquery.

The `LEFT JOIN … IS NULL` form is equally correct and is sometimes easier to read when you also want columns from the right side in the matched case. Its one hazard is that the `IS NULL` filter must be in the WHERE clause and must reference a column that is genuinely never NULL in B — filtering on a nullable column silently keeps rows that did match.

`NOT IN` is the trap. It reads the most naturally in English, which is exactly why it gets written, and it collapses to an empty result the moment the subquery yields a NULL. Use it only against a literal list or a column that is declared NOT NULL, and say which of those you are relying on.

The reasoning

The cost is the part people underestimate. Deduplication is not free filtering — the engine has to compare every row against every other, which means sorting or hashing the entire combined output. On a large union that can dominate the query, and it happens invisibly because nothing in the syntax suggests work.

The correctness risk is worse. If you union two months of orders and two genuinely identical orders exist — same customer, same amount, same second — `UNION` deletes one of them. You will never see an error; the total is just quietly short.

So the rule is: reach for `UNION ALL`, and switch to `UNION` only when you have a specific reason to believe duplicates exist and should go. If you need deduplication, doing it explicitly with a window function over a stated key is both cheaper to reason about and honest about which key defines "the same row".

The reasoning

A LEFT JOIN runs first and emits every left row, filling the right-hand columns with NULLs where nothing matched. The WHERE clause runs afterwards, over that result. So `WHERE r.status = 'paid'` is evaluated against a NULL status for exactly the rows the LEFT JOIN was protecting, yields UNKNOWN, and drops them.

The fix is to move the condition into the ON clause, where it participates in matching rather than in filtering: `LEFT JOIN returns r ON r.order_id = o.id AND r.status = 'paid'`. Now unmatched orders survive with NULL columns, which is what was intended.

The exception worth naming is `IS NULL`. `WHERE r.order_id IS NULL` in the WHERE clause is deliberate — that is the anti-join idiom, and it works precisely because it keeps the rows the join could not match.

The reasoning

After grouping, a row in the result is no longer a row from the table — it is a *set* of rows. Grouping columns are safe to select because every member of the set agrees on them by construction. Aggregates are safe because they define how to reduce many values to one. Anything else has no defined answer.

Dialects disagree about how to respond to the ill-posed question, and that is worth knowing. PostgreSQL and most warehouses reject it outright. SQLite and MySQL (outside strict mode) accept it and return an arbitrary value from the group, which is a silent, non-deterministic answer — the same query can return different values on the same data.

How arbitrary is arbitrary? While verifying the snippet below, adding `MIN(v)` and `MAX(v)` to the same query changed which value the bare column returned — from 1 to 2, on identical data. Nothing about the grouping changed; the plan did. That is what "unspecified" means in practice, and it is why a query relying on it can start returning different numbers after an engine upgrade with no code change at all.

The functional-dependency exception is real: if you group by a primary key, the standard permits selecting other columns of that table, because they are functionally determined by the key. PostgreSQL implements this. Relying on it makes the query harder to port, so most teams write it out anyway.

The reasoning

The logical order is FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then window functions, then QUALIFY, then SELECT, then DISTINCT, then ORDER BY, then LIMIT. The engine is free to execute it however it likes, but it must produce the answer this order defines — which is why the order explains so much of SQL's apparent inconsistency.

It explains, for instance, why you cannot use a SELECT alias in WHERE (SELECT has not happened yet) but can in ORDER BY (it has). It explains why HAVING can reference an aggregate and WHERE cannot. And it explains why filtering on a window function needs QUALIFY or a subquery — the window has not been computed when WHERE runs.

Prefer WHERE whenever the condition does not need an aggregate. It removes rows before grouping, so there is less to group; putting the same condition in HAVING gives the same answer for more work. QUALIFY is not standard SQL — Snowflake, BigQuery, Databricks and Teradata have it; PostgreSQL does not, and there you wrap the window in a subquery and filter outside.

The reasoning

Measures come in three kinds. Additive measures (revenue, quantity) can be summed across every dimension. Semi-additive measures (account balance, inventory on hand) can be summed across some dimensions but not time — you take the last value in the period, not the sum. Non-additive measures (ratios, percentages, distinct counts) cannot be summed at all.

The consequence is architectural. A daily aggregate table can serve monthly revenue directly, but it cannot serve monthly unique users at any accuracy — you have to go back to the row-level data, or store a sketch such as HyperLogLog that *can* be merged. Teams discover this when the monthly dashboard disagrees with the source and nobody can find a bug, because there is no bug.

The same reasoning kills averaging an average. The mean of daily averages is not the monthly average unless every day has identical volume. If you need to pre-aggregate, store the numerator and denominator separately and divide at read time.

The reasoning

Conditional aggregation is the workhorse: one aggregate per output column, each with a CASE that admits only the rows belonging to that column. It works everywhere, it is explicit about the aggregation, and unlike a dialect `PIVOT` clause it does not hide which function is being applied.

The structural problem is that SQL results have a fixed column list determined at parse time. A pivot over a value set you do not know in advance — a new product category appears next month — cannot be written as one static query. The real answers are to generate the SQL, to accept a fixed column list plus an "other" bucket, or to return long-format data and let the presentation layer pivot it.

Unpivoting is the reverse: many columns into rows, usually via `UNION ALL` of one SELECT per column, or a lateral join over a values list. It is more verbose but it is well-defined, and it has the advantage of producing a shape that survives new columns being added — which is exactly why analytical tables are usually stored long and pivoted at the edge.

The reasoning

That single property is what makes running totals, rankings, percent-of-total, and row-to-row comparisons expressible at all. `SUM(x) OVER (PARTITION BY customer)` gives every row its customer's total *next to* the row's own value; a GROUP BY would give you the total and lose the row.

It is also why windows compose where aggregates do not. `LAG` and `LEAD` reach into neighbouring rows, `ROW_NUMBER` assigns a position, and frames let each row see a moving slice of its partition. None of those questions can even be phrased in terms of collapsing.

The trade is cost and ordering. A window usually requires the engine to sort or hash-partition the input, and windows are computed after HAVING but before SELECT — which is why filtering on one needs QUALIFY or a wrapping subquery.

The reasoning

Adding ORDER BY to a window silently adds a frame. Without ORDER BY the frame is the whole partition; with it, the frame becomes everything from the start of the partition up to the current row *and every row tied with it*. That last clause is the surprise.

So a running total ordered by date, where two transactions share a date, gives both of those rows the total through the end of the day rather than one intermediate value and one final value. The output is monotonic and plausible, and it does not match a hand-computed running total — which is how people discover it, usually in a reconciliation meeting.

`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` is what most people mean, and it is worth writing explicitly every time. It gives each row its own position in the sequence, which means it also needs a deterministic tiebreaker in the ORDER BY or the assignment among tied rows is arbitrary.

The reasoning

With ties on the ordering column: ROW_NUMBER gives 1, 2, 3, 4 — it breaks the tie arbitrarily but always produces distinct values. RANK gives 1, 2, 2, 4 — tied rows share a rank and the next rank skips ahead. DENSE_RANK gives 1, 2, 2, 3 — tied rows share and nothing is skipped.

That makes ROW_NUMBER the dedup tool: partition by the key that defines a duplicate, order by whatever decides the winner, and keep `= 1`. Using RANK instead keeps *all* tied rows, so a duplicate whose ordering column is identical — which is the common case for an exact replay — survives deduplication entirely.

The catch with ROW_NUMBER is that arbitrary tie-breaking is non-deterministic. If two rows are genuinely identical on the ORDER BY columns, which one gets 1 can change between runs. Adding a stable tiebreaker such as the primary key makes the query reproducible, which matters as soon as anyone diffs two runs.

The reasoning

Two rows being duplicates is a claim about identity, and identity lives in the model, not in the data. `SELECT DISTINCT *` deduplicates on every column, which means two genuinely different events that happen to be identical get merged, and two copies of the same event that differ only in an ingestion timestamp both survive. Neither is what anyone wanted.

The second half is the survivorship rule. Latest wins, first wins, or highest-version wins are three different answers, and they produce different data. "Latest by ingestion time" is the usual choice for a replayed feed, "first" for an append-only log where corrections come later — but somebody has to decide, and it should be written down rather than inferred from whichever one the query happened to implement.

Once both are stated, the query is mechanical: `ROW_NUMBER() OVER (PARTITION BY <key> ORDER BY <survivorship>, <stable tiebreaker>)` and keep 1. The interesting work was all in the two sentences before it.

The reasoning

The distinction matters because inlining lets the optimizer push predicates into the CTE and combine it with the outer query, while materializing computes it once and reuses the result. Neither is universally better: materializing wins when the CTE is expensive and referenced several times, inlining wins when the outer query has a filter that would have removed most of the work.

The old PostgreSQL behaviour made `WITH` a deliberate optimization barrier, and a generation of engineers learned to use it as one. Since PostgreSQL 12 the default flipped, and `MATERIALIZED` / `NOT MATERIALIZED` were added so the intent can be stated explicitly. SQLite has the same hints. Snowflake, BigQuery and Spark generally inline.

The practical advice is to use CTEs for readability first — they are the single biggest lever on whether a long query can be reviewed — and to treat their performance behaviour as engine-specific, checking the plan when it matters rather than assuming. A CTE referenced once and inlined costs nothing.

The reasoning

The three steps are always the same. Order the events within each user; use `LAG(ts) OVER (PARTITION BY user ORDER BY ts)` to get the previous event time; mark a new session where the difference exceeds the timeout or where there is no previous event. Then `SUM(is_new_session) OVER (PARTITION BY user ORDER BY ts)` turns those flags into a session id that increments at each break.

That final running sum is the trick worth being able to explain — it converts a sparse set of boundary markers into a dense label for every row, and the same pattern solves every gaps-and-islands problem, not just sessionization.

The definitional half matters as much. Thirty minutes of inactivity is a convention, not a fact; some products use a fixed daily cut, some end a session on an explicit logout, some cap total duration. And it must be event time rather than arrival time, or a client that buffered offline events will appear to have had one long session at the moment its connection came back.

The reasoning

A dimension that keeps history has several rows per key, each with a validity window — `valid_from` and `valid_to`. The correct join adds a temporal predicate: `ON d.customer_id = o.customer_id AND o.ordered_at >= d.valid_from AND o.ordered_at < d.valid_to`. That returns exactly one row per order, because the intervals tile the timeline without overlapping.

The interval convention is load-bearing: closed at the start, open at the end. Using `<=` on both ends makes an order placed exactly on a boundary match two versions, which doubles the row and therefore the revenue. This is fan-out arriving through the time dimension rather than through the key.

The symptom of getting it wrong is a report that changes retroactively. Last quarter's revenue-by-tier moves every time a customer is upgraded, and nobody can find a bug because the query is doing exactly what it says. The general rule is that nothing which happened after an event may influence how that event is reported — the same rule that prevents leakage in a machine-learning training set.

The reasoning

For a MERGE the requirement is that the match condition uses a stable business key, and that the update is an assignment rather than an accumulation. `SET total = total + :amount` is not idempotent — a replay adds the amount twice. `SET total = :amount` is, because the result depends only on the input and not on the current state.

The second requirement is a version or timestamp guard, so a stale batch cannot overwrite newer data: apply the change only `WHEN MATCHED AND source.version > target.version`. Without it, a delayed retry of an old batch silently reverts a correction that landed in between.

The third is that the source of a MERGE must contain at most one row per key. Most engines raise an error when two source rows match the same target row, which is a good day — the bad day is the engine that picks one non-deterministically. Deduplicating the source to one winner per key, explicitly, is part of writing the MERGE rather than a precondition you hope for.

The reasoning

**The semantics.** `IN` compares a value against a set. `EXISTS` asks whether a correlated subquery returns any row at all — so it does not care what the subquery selects, which is why `SELECT 1` is idiomatic inside one.

**The difference that actually bites is NULL.** `x NOT IN (1, 2, NULL)` is not false, it is *unknown*, because SQL cannot prove `x <> NULL`. Unknown filters the row out, so the query returns zero rows — silently, with no error, and looking exactly like "there is no matching data". `NOT EXISTS` has no such problem: it is a straightforward "did the subquery produce a row", and a NULL in the subquery result changes nothing. This is why the reliable anti-join idiom is `NOT EXISTS`, and it is worth knowing that the same trap applies to `NOT IN` against any nullable column, not only against a literal list.

**The performance claim, corrected.** The folklore is that `EXISTS` short-circuits at the first match while `IN` materialises the whole subquery. That was true of older engines. Postgres, Snowflake, BigQuery, SQL Server and Oracle all rewrite both forms into the same semi-join, and the plans come out identical — you can verify it with `EXPLAIN` in about a minute. Asserting a performance difference without checking the plan on your engine is the mistake.

**So the honest answer** is: choose on correctness. Use `EXISTS`/`NOT EXISTS` for correlated existence checks, especially anti-joins, because it is NULL-safe. Use `IN` for a short literal list, where it reads better. If you genuinely need to know which is faster on your engine, read the plan rather than the internet.

The reasoning

**When they are the same.** `SELECT DISTINCT a, b FROM t` and `SELECT a, b FROM t GROUP BY a, b` return identical rows, and every mainstream optimizer compiles them to the same aggregate. Preferring one is a readability choice.

**When they differ.** `GROUP BY` can carry aggregates — `COUNT(*)`, `SUM(x)` — which `DISTINCT` cannot, and `GROUP BY` supports `HAVING`. `DISTINCT` applies to the whole select list after everything else, which is why `SELECT DISTINCT a, MAX(b)` is usually a sign that someone has conflated the two.

**Why it is expensive.** Deduplication means every row with the same values has to be compared against every other, so the engine either sorts or builds a hash table over the full projected width. On a single node that is CPU and memory, and it spills when the hash does not fit. **On a distributed engine it is a shuffle**: equal values must be collocated to be compared, so rows move across the network — which is why a `DISTINCT` on a wide, high-cardinality projection can dominate a query that was otherwise a straight scan.

**The reason to be suspicious of it.** Most `DISTINCT`s in production SQL are not a requirement — they are a fan-out being papered over. A join against a dimension that is not unique on its key duplicates rows, someone adds `DISTINCT`, the row count looks right, and the sums are still wrong because `SUM` was already computed over the duplicates. When you see one, the question to ask is *why* there are duplicates, not how to remove them.

The reasoning

**The classical distinction.** A table has at most one primary key. It cannot be null, because it identifies the row, and it is what foreign keys reference. A table can have any number of unique constraints, and most engines allow a null in a unique column — sometimes several, since two nulls are not equal to each other. In practice the primary key is the identity and unique keys are the other invariants: an email that must not repeat, a natural key that must stay one-to-one.

**The part that matters for data engineering.** Snowflake, BigQuery and Redshift all let you *declare* primary and unique keys and none of them enforce it. The declaration is metadata: the optimizer may use it to eliminate a join or skip a deduplication step, and nothing stops you inserting the same key twice. **That is the trap** — an optimizer trusting a uniqueness claim that the data violates can return wrong results, not just slow ones.

**So uniqueness in a warehouse is something you test, not something you declare.** A dbt `unique` test, a scheduled assertion, or a check in the load itself is what actually holds the invariant. Declaring the constraint is still worth doing for documentation and for the optimizer, but it is not a guarantee and treating it as one is how duplicate rows reach a dashboard.

**Surrogate versus natural** is the adjacent question and usually the follow-up: the primary key of a warehouse dimension is typically a generated surrogate, precisely because natural keys change, get reused, and arrive from several source systems with different formats.

The reasoning

**Logical** answers "what is this data": the entities, their attributes, the relationships between them, the grain of each table, and the keys that identify rows. It is engine-independent — the same logical model can be implemented on Postgres, Snowflake or a pile of Parquet, and it should not change when you migrate.

**Physical** answers "how is it stored": concrete data types and precision, the partition column, clustering or sort keys, distribution style, compression, file sizes, indexes, materialisation. All of it is engine-specific and all of it is chosen for the access pattern, not for the meaning.

**Why the separation earns its keep.** Two failure modes come from collapsing them. One is a correct model that performs terribly — right grain, right keys, partitioned on a column nobody filters by. The other, worse, is physical concerns leaking into the model: a table denormalised because a join was slow, a column duplicated to avoid a lookup, a grain coarsened because storage was expensive. Now the meaning of the data depends on the engine, and the next migration is a redesign.

**A partition key is physical**, and so is a distribution key, a sort key and a clustering key. None of them changes what a row means. The grain, by contrast, is logical — "one row per order line" is a statement about meaning — and it is the thing people most often confuse with partitioning, because both are about how rows are divided up.

The reasoning

**The definition.** Sort the observations. p90 is the value at the position 90% of the way along. With 26 rows the nearest-rank target is `ceil(26 × 0.9)` = 24, so p90 is the 24th smallest value. That is `PERCENTILE_DISC` — discrete — and it always returns an observed value.

**The other definition interpolates.** `PERCENTILE_CONT` treats the position as continuous: rank 23.4 means 40% of the way between the 23rd and 24th values, and it returns the weighted average of the two. On any dataset that is not perfectly even, the two disagree, and they disagree most in the tail — the exact region an SLA is written about. For a target anyone will be held to, DISC is the defensible choice: "90% of requests completed within 210ms" is a claim about real requests.

**Why it beats the mean.** The mean answers "the total, spread evenly", so a single four-hour outlier moves it and nothing else does. It is common for the mean to sit above p75 — meaning the *average* request is slower than three quarters of all requests, which is a sentence that stops a room.

**The rule people break most:** percentiles do not add up. The p90 of the week is not the average of seven daily p90s, and you cannot combine two services p90s into a system p90. They have to be recomputed from the underlying observations, which is why storing pre-aggregated percentiles is a trap.

**Portability.** PostgreSQL and Snowflake spell it `PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY x)`; BigQuery has `APPROX_QUANTILES` and a windowed form; SQLite has none of them, so you rank with `ROW_NUMBER()` and pick the row yourself. Knowing the rank arithmetic is what makes the function optional.

The reasoning

**First, make the ordering total.** Pagination is a position in an ordering, so if two rows compare as equal the boundary between pages is undefined and may move between queries. Append a unique column — `ORDER BY created_at DESC, id DESC`. Without it a row can appear on two pages while another is never shown, and the bug is unreproducible because it depends on the plan.

**Then choose the mechanism.** `LIMIT n OFFSET (page - 1) * n` is simple and supports jumping to page 47, which admin tables want. Its cost grows with the offset: the engine produces every skipped row and throws it away, so page 900 does nine hundred pages of work, and rows inserted while a user pages shift every later page.

**A keyset cursor fixes both.** The client sends back the last row it displayed and the query says "start strictly after that row": `WHERE (created_at, id) < (:last_at, :last_id)` with the same ORDER BY. The cost is flat — page 900 costs what page 2 costs — and concurrent inserts cannot shift a boundary that names a row rather than a position. The predicate must match the sort keys exactly; comparing on the timestamp alone silently skips every row that ties with the cursor.

**What you give up** is jumping to an arbitrary page, so cursors suit feeds and infinite scroll while OFFSET suits short admin tables. The other question worth asking is whether the screen needs a total count at all — `COUNT(*)` over the whole table frequently costs more than the page it decorates.

The reasoning

**There is no single answer, which is the answer.** SQLite's `LIKE` is case-insensitive for ASCII characters (and only ASCII — accented characters are not folded). PostgreSQL's is case-sensitive and offers `ILIKE` as a non-standard alternative. MySQL follows the column's collation, so the same query on two columns in one database can behave differently.

**That is exactly how the reported bug happens:** developed against SQLite where `LIKE '%ada%'` matched `Ada Okafor`, deployed to PostgreSQL where it does not. Nothing errors. The search box just quietly returns fewer results, and the report reads as "search is broken sometimes".

**The portable fix is `LOWER(col) LIKE '%ada%'`** with a lowercase pattern. It states the intent instead of depending on a default, and it behaves identically everywhere. `ILIKE` looks tidier and does not exist outside PostgreSQL, so it trades one portability problem for another.

**The cost, and what to do about it.** Wrapping the column in `LOWER()` stops a plain index on that column from being used, so the engine scans. On a small table that is irrelevant; at scale the fix is to normalize on write — store a lowercased search column — or to build a functional index on `LOWER(col)` where the engine supports one. Note also that a leading `%` prevents index use regardless of case, because a B-tree can only seek on a known prefix.

The reasoning

**They compare whole rows, matched by position.** `SELECT a, b FROM x INTERSECT SELECT c, d FROM y` matches when a = c and b = d. Column names are irrelevant and only the order matters, which is both the convenience and the trap: swap two compatible columns in one branch and the query runs and matches nothing.

**They deduplicate as part of their definition.** The equivalent join needs a `DISTINCT` to be correct — a customer with three orders in a matching month produces three joined rows. Forgetting that `DISTINCT` is how "customers in both channels" quietly doubles.

**NULL is the sharpest difference.** Set operators treat two NULLs as the same value; `=` treats the comparison as unknown. So `EXCEPT` handles a nullable comparison column correctly where `NOT IN` returns zero rows the moment one NULL appears in the subquery — silently, and looking exactly like "there is nothing to report".

**When to use something else.** If you need columns from the left side that are not part of the comparison, a set operator cannot give them to you — that is `NOT EXISTS`, which is equally NULL-safe and lets you keep the whole row. If you need duplicate multiplicity preserved, you want `INTERSECT ALL`/`EXCEPT ALL`, which several engines do not implement, so a join with an explicit count is more portable.

**Dialect note:** Oracle spells `EXCEPT` as `MINUS`. Everything else is standard and widely supported.

The reasoning

**What it does.** `GROUP BY GROUPING SETS ((country, city), (country), ())` produces all three levels in one result from one pass. `ROLLUP(country, city)` is shorthand for exactly those three, in that hierarchy; `CUBE` produces every combination, which is 2^n levels and almost never what a report wants.

**Why not three UNION ALL branches.** They are correct and they scan the table once per branch. On a large fact table that is three times the IO for a result the engine can produce in one aggregation. The stacked version also has to be kept consistent by hand — a filter added to one branch and not the others is invisible in review.

**The ambiguity you must handle.** A rolled-up level emits NULL in the columns it collapsed. A row reading `(US, NULL, 4300)` might be "all cities in the US" or "US orders whose city was never recorded", and nothing in the output distinguishes them. `GROUPING(city)` returns 1 in the first case and 0 in the second — that is the entire reason the function exists.

**What to ship.** Project a real level column: `GROUPING(city) AS is_city_total`, or a text label built from it. Consumers can then filter or style on a value rather than pattern-matching on NULL. Order by the grouping flags first so subtotals sit under their detail rather than sorting alphabetically into the middle.

**And the measure caveat.** Sums and counts roll up. Averages, ratios and `COUNT(DISTINCT …)` do not — the distinct customers across two cities is not the sum of each city's distinct customers. Non-additive measures have to be recomputed at every level, which GROUPING SETS does correctly and a hand-rolled union of pre-aggregated results usually does not.

The reasoning

**The termination rule.** The engine evaluates the anchor, then repeatedly evaluates the recursive term against the rows produced by the previous iteration. It stops when an iteration returns zero rows. In a genuine tree that always happens: eventually you reach the leaves, nothing has a parent among them, and the walk ends.

**Why a cycle breaks it.** If employee A reports to B and B reports to A, every iteration finds rows, so the loop never empties. `UNION ALL` does not help — it does not check whether it has seen a row before. The query runs until it exhausts memory or hits an engine limit, and on a shared warehouse that is an incident rather than a slow query.

**Three guards, in order of portability.** Carry a depth counter and add `WHERE depth < 50` to the recursive term — portable, cheap, and it caps the damage even if the data is fine. Carry the visited path as a string or array and refuse to recurse into a node already in it — exact, and more expensive. Or use the engine limit: SQL Server has `OPTION (MAXRECURSION n)`, Postgres has no per-query limit but will respect a statement timeout.

**`UNION` instead of `UNION ALL` is the fourth option and the one to be careful with.** It deduplicates each iteration, so a DAG that reaches the same node by two routes converges; a true cycle among distinct rows still loops, and you have paid for a sort on every step. Use it deliberately for graphs, not as a reflex.

**Where the bound goes matters.** In the recursive term it stops the engine descending. In the final SELECT it filters rows the engine has already produced — same result on good data, no protection at all against a cycle.