Logical query processing order, JOIN semantics and row-count reasoning, set operations, three-valued NULL logic, and tracing a query step by step

SQL Mental Model & Query Execution Order

Every data engineering interview loop assumes you can write SQL. Very few loops actually test whether you can write it — they test whether you understand what the database does with it. That distinction is the entire subject of this page. A candidate who has memorized SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY as a fixed template can write queries that work most of the time, right up until they hit a query that silently returns the wrong answer, or an error message that makes no sense given the query "looks fine." A candidate who has internalized the logical order of evaluation — a fixed sequence the engine walks through regardless of how the query is typed — can explain, from first principles, why a WHERE clause can't reference a SELECT alias, why COUNT(*) and COUNT(column) disagree in the presence of NULL, why a LEFT JOIN followed by a careless WHERE silently turns back into an INNER JOIN, and why NOT IN against a column containing even one NULL can make an entire query return zero rows for no visible reason.

This gap — between "I can write a query that works on the happy path" and "I can predict what a query does before running it" — is exactly what interviewers are probing for when they hand you a query with a subtle bug and ask "what does this return?" instead of "write a query that does X." It is also, unglamorously, the single highest-leverage thing to get solid before anything else in this track: window functions (advanced-sql-window-functions-and-ctes), query optimization (sql-query-optimization-and-indexing), and every case study that follows all assume this mental model is already load-bearing, not something you're deriving from scratch mid-interview. This subject builds it from the ground up, with runnable SQL at every step, ending in a full trace of a non-trivial query the way you'd walk an interviewer through one on a whiteboard.

Written Order vs. Logical Execution Order

Here is a query written the way every SQL tutorial teaches you to write it:

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY headcount DESC
LIMIT 10;

The clauses appear in this order on the page: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. That is the written order — a syntax rule enforced by the parser, and nothing more. It has almost nothing to do with the order the database actually evaluates the query in. The logical processing order — the order in which a relational engine conceptually builds and narrows down the result set, step by step — is different, and it is the order that actually explains the behavior of every clause:

FROM / JOIN — build the initial working set of rows, resolving every table and join in the query.
WHERE — filter individual rows out of that working set, before any grouping happens.
GROUP BY — collapse the remaining rows into groups, one row per distinct group key.
HAVING — filter groups (not rows) based on aggregate conditions.
SELECT — compute the actual output columns and expressions, including aggregates and aliases.
DISTINCT — deduplicate the resulting rows, if requested.
ORDER BY — sort the final result set.
LIMIT / OFFSET — cut the sorted result down to the requested window.

Every real relational database (PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery) follows this conceptual sequence when determining what a query means, even though the actual physical execution plan the optimizer builds can reorder, fuse, or parallelize steps for performance as long as the observable result is identical to what this logical order would produce. That last clause matters: the optimizer is free to push a WHERE predicate down into a join, or filter before it aggregates for efficiency, but it is never free to change what the query returns. The logical order is a contract about semantics, not a description of physical execution — the physical plan is what sql-query-optimization-and-indexing is about; this subject is about getting the semantics right first, because an optimizer that runs the wrong query faster is not helping you.

Why this explains the classic "why can't I use a SELECT alias in WHERE" confusion. Consider:

SELECT price * quantity AS line_total
FROM order_items
WHERE line_total > 100;  -- ERROR in most databases

This fails (or, in a few databases, is quietly not what you think) because of the logical order: WHERE is evaluated in step 2, and SELECT — where line_total is defined — is evaluated in step 5. At the point WHERE runs, line_total does not exist yet; the engine has no idea what it means, because the column list hasn't been computed. The fix is to either repeat the expression or wrap the query:

SELECT price * quantity AS line_total
FROM order_items
WHERE price * quantity > 100;

-- or, using a subquery/CTE so the alias exists by the time it's filtered on:
SELECT line_total FROM (
  SELECT price * quantity AS line_total
  FROM order_items
) t
WHERE line_total > 100;

The same logic explains why GROUP BY can reference a SELECT alias in some databases (MySQL, PostgreSQL, BigQuery allow it as a convenience) even though GROUP BY runs in step 3 and SELECT in step 5 — that's a documented, database-specific relaxation of the strict logical order for ergonomics, not evidence the logical order isn't real. HAVING, by contrast, is allowed to reference aggregate expressions and, in many databases, SELECT aliases too, because it's evaluated after grouping and close enough to SELECT in the pipeline that most engines resolve it against the same expression list. The one clause that can freely use a SELECT alias everywhere, with no caveats, is ORDER BY — it runs dead last, after the output columns are fully materialized:

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;  -- fine — SELECT has already run

A second consequence: WHERE can't filter on aggregates, HAVING can.

-- WRONG: WHERE runs before GROUP BY, so no aggregate exists yet to filter on
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE COUNT(*) > 5      -- ERROR
GROUP BY department;

-- RIGHT: HAVING runs after GROUP BY, once aggregates exist
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

This is not an arbitrary rule to memorize — it falls straight out of the logical order. WHERE operates on individual rows before grouping exists; HAVING operates on groups after grouping has happened. Internalizing the eight-step order means you stop needing to memorize a table of "which clause can reference what" and start deriving it on the spot, which is exactly what an interviewer wants to see when they hand you an unfamiliar query.

JOIN Types: Reason by Row Count, Not by Venn Diagram

Venn diagrams are how JOINs get taught, and they are also how candidates get tripped up, because a Venn diagram tells you nothing about cardinality — how many rows come out. The more reliable mental model: a JOIN's job is to produce, for every combination of rows on each side that satisfies the join condition, one output row. Everything else — INNER vs LEFT vs FULL — is just a rule about what happens to rows that don't find a match.

Set up two small tables to reason about concretely:

-- customers (3 rows)
customer_id | name
------------+-------
1           | Aisha
2           | Ben
3           | Chen

-- orders (4 rows)
order_id | customer_id | amount
---------+-------------+-------
101      | 1           | 50
102      | 1           | 30
103      | 2           | 20
104      | 9           | 15    -- customer_id 9 doesn't exist in customers

Note deliberately: Aisha has two orders, Chen has zero orders, and there's an order (104) with a customer_id that doesn't exist in customers at all — this is the setup that makes every JOIN type produce a different row count, which is the point.

INNER JOIN — keep only rows where the join condition matches on both sides.

SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Result: 3 rows — Aisha/101, Aisha/102, Ben/103. Chen drops out (no matching order), and order 104 drops out (no matching customer). Row count is driven entirely by how many (customer, order) pairs satisfy the condition — here, 3.

LEFT JOIN — keep every row from the left table, matched where possible, NULL-filled where not.

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Result: 4 rows — Aisha/101, Aisha/102, Ben/103, and Chen/NULL/NULL. Chen is preserved because she's guaranteed to appear at least once regardless of matches; order 104 is still dropped because it lives on the right side, which the LEFT JOIN makes no promise about. This is the join you reach for whenever the question is "give me every X, along with its Y if it has one" — every customer regardless of order history, every product regardless of whether it sold, every day in a date range regardless of whether an event happened on it.

RIGHT JOIN — the mirror image: keep every row from the right table.

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Result: 4 rows — Aisha/101, Aisha/102, Ben/103, and NULL/104/15. Order 104 is preserved with a NULL customer name because it's guaranteed to appear regardless of a match on the left. In practice, RIGHT JOIN is rare in production code — almost anything expressed with RIGHT JOIN reads more clearly rewritten as a LEFT JOIN with the tables swapped, and most style guides ban it for that reason. It's worth knowing cold for an interview, not for your own code.

FULL OUTER JOIN — keep every row from both sides, matched where possible.

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

Result: 5 rows — the 3 matched rows, plus Chen (unmatched left), plus order 104 (unmatched right). This is the join for reconciliation-style questions: "which records exist on one side but not the other, in either direction" — a very common data-quality task, covered from the pipeline-monitoring angle in data-quality-testing-and-observability.

CROSS JOIN — every row on the left paired with every row on the right, no condition at all. Row count is a straight multiplication: 3 customers × 4 orders = 12 rows, none of them filtered by any relationship. This is correct on the rare occasion you actually need a full Cartesian product (generating a calendar-dimension table, pairing every store with every product to seed a zero-filled inventory table) and a bug almost every other time — an accidental CROSS JOIN is usually a missing or mistyped ON condition, and it's the single most common cause of a query that "looks right" but returns far more rows than expected, often silently, because the query doesn't error, it just multiplies.

SELF JOIN — not a different keyword, just a table joined to itself, useful whenever a row references another row in the same table: an employee referencing their manager (also an employee), a directed edge in a graph stored as (from_id, to_id), a sessions table where you want each event paired with the next event for the same user.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

The LEFT JOIN here (rather than INNER) matters for the same reason it always does: without it, employees with no manager (typically the CEO, manager_id IS NULL) would silently disappear from the result, because NULL never equals anything — the topic of the NULL section below.

The row-count-reasoning habit, generalized. Before running any JOIN, ask two questions: (1) for a given row on the "preserved" side, how many matches could it have on the other side — zero, one, or many? (2) does the JOIN type guarantee that row survives even with zero matches? A LEFT JOIN where the right side can match multiple rows will duplicate the left row once per match — this is the second most common cause of a query silently returning more rows than expected (the first being an accidental CROSS JOIN), and it's why "I joined in an aggregate and my SUM is now way too high" is one of the most common SQL bugs in production pipelines: a LEFT JOIN to a table with multiple matching rows per key inflates the base row count before any aggregation happens, and the resulting SUM/COUNT is computed over the inflated set.

The Silent LEFT JOIN → INNER JOIN Trap

This deserves its own section because it is one of the most commonly cited "gotcha" questions in SQL interviews, and it flows directly from the execution order established above. Consider a query meant to find every customer, including those with no orders, but only orders placed this year:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01';   -- looks harmless

The intent is "left join to preserve customers without orders, then optionally narrow by date." But recall the logical order: FROM/JOIN runs first and produces the outer-joined rows, including Chen's row with order_id = NULL and order_date = NULL — but WHERE runs after that, in step 2, and filters the row-by-row result. NULL >= '2026-01-01' is not TRUE — under three-valued logic (covered fully below) it's UNKNOWN, and WHERE only keeps rows where the condition evaluates to TRUE. Chen's NULL-filled row gets silently filtered out by the WHERE clause, which defeats the entire purpose of using LEFT JOIN in the first place — the query behaves exactly like an INNER JOIN would have, just with more typing and no error to signal the mistake.

The fix is to move the date condition into the ON clause, so it's applied during the join (step 1) rather than after it:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
  AND o.order_date >= '2026-01-01';

Now Chen is still preserved (she has no matching order at all, so the AND condition never has a chance to exclude her — she was never matched to begin with), while a customer whose only orders are from a prior year also correctly shows up with a NULL order rather than disappearing. The general rule: conditions on the preserved (left) table belong in WHERE; conditions on the optional (right) table belong in the ON clause, if you want unmatched left rows to survive. Conflating the two is the single most common way a LEFT JOIN silently stops doing anything.

Set Operations: UNION, UNION ALL, INTERSECT, EXCEPT

Set operations combine the results of two (or more) queries with the same number of columns and compatible types, rather than combining rows within a single row-set the way a JOIN does. The mental model is exactly what the names suggest if you think of each query's result as a mathematical set of rows.

UNION vs UNION ALL — the distinction that trips people up most:

SELECT customer_id FROM active_customers
UNION
SELECT customer_id FROM trial_customers;

UNION combines both result sets and removes duplicate rows — it's a set union in the literal mathematical sense, which means the engine has to do the equivalent of a sort or hash pass over the combined output to deduplicate, an operation with a real performance cost.

SELECT customer_id FROM active_customers
UNION ALL
SELECT customer_id FROM trial_customers;

UNION ALL combines both result sets and keeps every row, duplicates included — no deduplication pass, so it's strictly cheaper and faster. The rule of thumb worth stating explicitly in an interview: default to UNION ALL, and only pay for UNION's deduplication when you have a concrete reason to believe duplicates can occur and actually need removing. A very common mistake is reflexively typing UNION out of habit on two queries that are already guaranteed disjoint (e.g., one filters status = 'active' and the other status = 'trial' from the same table, which can never overlap) — paying a deduplication cost for a guarantee that was already true for free.

INTERSECT — rows that appear in both result sets:

SELECT customer_id FROM active_customers
INTERSECT
SELECT customer_id FROM newsletter_subscribers;
-- customers who are both active AND subscribed

EXCEPT (called MINUS in Oracle) — rows in the first result set that do not appear in the second, i.e., set difference, and order matters:

SELECT customer_id FROM active_customers
EXCEPT
SELECT customer_id FROM newsletter_subscribers;
-- active customers who are NOT subscribed

Swapping the two queries changes the answer (newsletter_subscribers EXCEPT active_customers would give subscribers who aren't active customers) — unlike UNION/INTERSECT, which are symmetric, EXCEPT is not, and interviewers occasionally probe exactly this with "does order matter here?"

Set operations vs JOINs — when to reach for which. A JOIN combines columns from two tables side by side, row by row, based on a matching condition; a set operation stacks rows from two same-shaped queries on top of each other (or subtracts/intersects them), with no side-by-side column combination at all. "Customers with an order in both January and February" is an INTERSECT (or an equivalent EXISTS-based query) because you're comparing sets of customer IDs; "each order alongside its customer's name" is a JOIN because you're combining columns. Reaching for a JOIN when you actually want an EXCEPT/INTERSECT (or the reverse) is a common source of overcomplicated queries — if the real question is "which IDs are in A but not B," write EXCEPT and stop there rather than building a LEFT JOIN ... WHERE right.id IS NULL (which is a legitimate, commonly used equivalent, but worth recognizing as equivalent rather than reaching for by default).

NULL and Three-Valued Logic

NULL represents "unknown" or "absent," not zero, not an empty string, and not "false." This single fact is the root cause of more SQL bugs than any other single concept in the language, because it means SQL's boolean logic is not two-valued (TRUE/FALSE) — it's three-valued: TRUE, FALSE, and UNKNOWN.

Any comparison involving NULL evaluates to UNKNOWN, never TRUE or FALSE.

NULL = NULL         -- UNKNOWN, not TRUE
NULL <> NULL        -- UNKNOWN, not TRUE
NULL = 5             -- UNKNOWN
5 > NULL             -- UNKNOWN

This is the reason WHERE column = NULL never matches any row, even rows where column genuinely is NULL — the comparison isn't TRUE, it's UNKNOWN, and WHERE only keeps rows that evaluate to exactly TRUE. The only correct way to test for NULL is the dedicated predicate:

WHERE column IS NULL
WHERE column IS NOT NULL

AND/OR with UNKNOWN follow specific rules, not "propagate NULL blindly":

TRUE  AND UNKNOWN  -- UNKNOWN
FALSE AND UNKNOWN  -- FALSE   (a FALSE anywhere in an AND forces FALSE regardless of the other side)
TRUE  OR  UNKNOWN  -- TRUE    (a TRUE anywhere in an OR forces TRUE regardless of the other side)
FALSE OR  UNKNOWN  -- UNKNOWN

The pattern: UNKNOWN only "wins" when the other operand couldn't force the answer on its own — FALSE AND anything-including-UNKNOWN is always FALSE, and TRUE OR anything-including-UNKNOWN is always TRUE, because those outcomes are already determined regardless of what the unknown value turns out to be.

COUNT(*) vs COUNT(column) disagree in the presence of NULL:

-- orders: 5 rows, 2 of which have a NULL discount_code
SELECT COUNT(*)             AS total_rows,        -- 5: counts rows, NULLs included
       COUNT(discount_code) AS rows_with_discount  -- 3: counts non-NULL values only
FROM orders;

COUNT(*) counts rows regardless of content; COUNT(column) counts only rows where that specific column is non-NULL. Conflating the two is a common source of an off-by-however-many-NULLs-exist bug in reporting queries. The same asymmetry applies more subtly to SUM, AVG, MIN, MAX — they all silently ignore NULL values rather than treating them as zero, which is correct behavior once you know it and a source of surprising results if you don't (AVG over a column with NULLs divides by the count of non-NULL rows, not the total row count — a common reason a computed average looks "too high").

The Classic NOT IN + NULL Trap

This is one of the most-cited SQL interview traps for good reason — it produces a query that returns zero rows, with no error, no warning, on data that looks completely reasonable.

-- blocked_customer_ids: (5, 12, NULL)  -- one NULL snuck in, maybe from a bad ETL join upstream
SELECT *
FROM customers
WHERE customer_id NOT IN (SELECT blocked_id FROM blocked_customer_ids);

Walk through what NOT IN actually expands to: customer_id NOT IN (5, 12, NULL) is logically equivalent to customer_id <> 5 AND customer_id <> 12 AND customer_id <> NULL. That last comparison, customer_id <> NULL, is UNKNOWN for every single row, regardless of what customer_id is — comparisons against NULL are always UNKNOWN, never TRUE. And an AND chain where one term is UNKNOWN and no term is FALSE evaluates to UNKNOWN overall (per the three-valued table above) — which WHERE then discards, because it only keeps TRUE. The result: every row is filtered out, silently, because a single NULL anywhere in the NOT IN subquery's result poisons the entire condition.

-- SAFE: explicitly exclude NULLs from the subquery before comparing
SELECT *
FROM customers
WHERE customer_id NOT IN (
  SELECT blocked_id FROM blocked_customer_ids WHERE blocked_id IS NOT NULL
);

-- SAFER STILL: NOT EXISTS sidesteps the NULL trap entirely, because it never
-- does a direct equality comparison against the subquery's rows as a set
SELECT *
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM blocked_customer_ids b WHERE b.blocked_id = c.customer_id
);

The interview-ready takeaway: prefer NOT EXISTS over NOT IN whenever the subquery's column is nullable and you can't fully guarantee it's clean — NOT EXISTS correlates row by row and never constructs the poisoned <> NULL comparison in the first place, so it degrades gracefully instead of silently returning nothing. IN (without the NOT) doesn't have this problem — customer_id IN (5, 12, NULL) just fails to match the NULL entry and correctly matches on 5 and 12 — the trap is specific to negation.

GROUP BY and HAVING as a Mental Model

GROUP BY collapses many rows into one row per distinct combination of the grouping columns — think of it as physically sorting the rows into buckets, one bucket per unique key, and then computing exactly one output row per bucket. This immediately explains the rule that trips up almost everyone learning SQL: every column in the SELECT list must either be in the GROUP BY list, or be wrapped in an aggregate function (COUNT, SUM, AVG, MIN, MAX, and friends). Once a bucket has collapsed ten rows into one, SELECT can no longer ask "what was the value of some ungrouped column" — there isn't a single answer, there were ten, so the engine has to be told how to reduce them to one (an aggregate) or told that the column is itself part of what defines the bucket (a GROUP BY key).

SELECT department, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title;

Here, each output row represents one (department, job_title) pair — the bucket — and AVG(salary) reduces every salary within that bucket to a single number. Ask for manager_name in the SELECT list without adding it to GROUP BY or wrapping it in an aggregate, and most databases will reject the query outright (a few, notably older MySQL configurations, will silently pick an arbitrary manager_name from within the group — which is almost never what you want, and is exactly the kind of database-specific relaxation worth knowing exists so you don't get burned by it).

HAVING filters groups, not rows — and that's the entire reason it exists as a separate clause from WHERE. By the time HAVING runs (step 4), individual rows no longer exist in the pipeline; only buckets do. HAVING COUNT(*) > 5 asks "keep this bucket only if it had more than 5 rows in it" — a question that's meaningless before grouping has happened, which is exactly why WHERE can't ask it (recall step 2 runs before step 3).

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'   -- filter ROWS: only recent hires, before grouping
GROUP BY department                -- collapse into one bucket per department
HAVING COUNT(*) > 5                -- filter GROUPS: only departments with >5 recent hires
ORDER BY avg_salary DESC;

Read that query as a pipeline rather than a flat list of clauses: start with all employees, throw out anyone hired before 2020, bucket what's left by department, throw out any bucket with 5 or fewer people in it, then sort what remains by average salary. That's the mental model this entire subject is building toward — every clause is a step in a pipeline that narrows or reshapes a working set, and the only way to reliably predict a query's output is to walk that pipeline in logical order, not written order.

Worked Example: Tracing a Query Step by Step

Put everything together on one query, the way you'd narrate it out loud in an interview. Schema: orders(order_id, customer_id, order_date, amount, status) and customers(customer_id, name, region).

SELECT
  c.region,
  COUNT(DISTINCT o.customer_id) AS active_customers,
  SUM(o.amount) AS total_revenue
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
  AND o.status = 'completed'
WHERE c.region IS NOT NULL
GROUP BY c.region
HAVING SUM(o.amount) > 10000
ORDER BY total_revenue DESC
LIMIT 5;

Trace it in logical order, not written order:

FROM / JOIN. Start with customers. For each customer, LEFT JOIN to orders, but only to orders where status = 'completed' — that condition lives in ON, not WHERE, deliberately, so a customer with only cancelled orders is preserved with a NULL-filled order row rather than disappearing (exactly the pattern from the "silent LEFT JOIN trap" section above). A customer with three completed orders now produces three rows in this intermediate working set, each with the customer's columns repeated alongside one order's columns; a customer with zero completed orders produces exactly one row, with every order column NULL.
WHERE. Filter that row-by-row working set down to rows where c.region IS NOT NULL. This runs on individual rows, before any grouping, and correctly uses IS NOT NULL rather than <> NULL — a direct application of the NULL section above. Note this condition is about the customer, the "preserved" side of the join, which is exactly where a WHERE condition belongs per the rule established earlier.
GROUP BY. Collapse the surviving rows into one bucket per distinct c.region. All the individual order rows for every customer in, say, the 'APAC' region — across every customer, across every one of their completed orders — now belong to a single 'APAC' bucket.
HAVING. For each region-bucket, compute SUM(o.amount) over every order row in that bucket and keep only buckets where the total exceeds 10000. A region where completed orders exist but don't cross that threshold is dropped here — after grouping, before output.
SELECT. For each surviving bucket, compute the three output expressions: c.region (the grouping key, one value per bucket by construction), COUNT(DISTINCT o.customer_id) (count of distinct customers with at least one completed order in that region — the DISTINCT matters because a customer with three completed orders should count once as "active," not three times), and SUM(o.amount) (recomputed here for output, same aggregate HAVING already evaluated once to filter — most engines optimize this so the aggregate isn't literally computed twice, but logically it's the same value).
DISTINCT. Not used in this query — no deduplication of output rows requested beyond what GROUP BY already guarantees (one row per region).
ORDER BY. Sort the surviving region-rows by total_revenue descending — legal here, and only here, because total_revenue the alias now exists; SELECT has already run.
LIMIT. Cut the sorted list down to the top 5 regions by revenue.

Notice what this trace makes obvious that reading the query top-to-bottom does not: COUNT(DISTINCT o.customer_id) and SUM(o.amount) are computed once, per bucket, over every order row that survived steps 1–3 — which means a customer with NULL order columns (because they had zero completed orders) contributes NULL to that customer's row in the working set, and SUM/COUNT both correctly ignore NULL per the earlier section on aggregates, so that customer doesn't inflate active_customers and doesn't corrupt total_revenue. Nothing about that behavior is stated anywhere in the query text — it falls entirely out of understanding steps 1, 2 (via NULL handling in aggregates). This is the payoff of the mental model: once you can run this trace on autopilot, "what does this query return" stops being a guess and becomes a mechanical walk through eight well-defined steps.

Follow-Up Questions Interviewers Ask

"Why does WHERE run before GROUP BY but HAVING runs after — walk me through why, not just what." — Because WHERE filters individual rows, which only exist before grouping collapses them into buckets; HAVING filters buckets, which only exist after grouping. Trying to filter on an aggregate in WHERE fails because no aggregate value exists yet at that point in the logical pipeline.
"I have a LEFT JOIN that's behaving exactly like an INNER JOIN — where would you look first?" — Check whether a condition on the right-hand (optional) table has been placed in WHERE instead of ON; a NULL-filled unmatched row almost always fails a WHERE condition on a column from the table it didn't match, silently discarding it and defeating the outer join.
"A NOT IN query is returning zero rows and the data looks fine at a glance. What's your hypothesis?" — Check whether the subquery feeding NOT IN can produce a NULL — a single NULL in that list poisons every row's comparison to UNKNOWN, which WHERE discards wholesale. Reach for NOT EXISTS or explicitly filter IS NOT NULL in the subquery.
"When would you use UNION instead of UNION ALL, and what's the cost of getting it backwards?" — Use UNION only when duplicates are actually possible and need removing; defaulting to UNION when the two queries are already guaranteed disjoint pays an unnecessary deduplication cost. Getting it backwards the other way — using UNION ALL when duplicates genuinely need removing — silently corrupts downstream counts or sums with duplicated rows.
"Rewrite this query to use EXCEPT instead of the LEFT JOIN ... WHERE right.id IS NULL pattern it currently uses — are they equivalent?" — Generally yes for finding "in A but not B" on a single key column; EXCEPT is often clearer to read and can be easier for the optimizer to recognize as an anti-join, while the LEFT JOIN form generalizes more easily when you need columns from both sides in the output, not just the "missing" rows.

Common Mistakes and Interview Traps

Treating written order as execution order, and being unable to explain why a SELECT alias can't be used in WHERE beyond "that's just the rule."
Comparing to NULL with = NULL or <> NULL instead of IS NULL / IS NOT NULL, and not knowing that the former always evaluates to UNKNOWN, never TRUE.
Using NOT IN against a subquery result that can contain NULL, producing a query that silently returns zero rows with no error.
Putting a filter on the "optional" side of a LEFT JOIN into WHERE instead of ON, silently degrading it into an INNER JOIN.
Defaulting to UNION out of habit when UNION ALL is correct and cheaper, or the reverse — using UNION ALL when duplicate rows would silently inflate a downstream SUM/COUNT.
Confusing COUNT(*) (counts rows) with COUNT(column) (counts non-NULL values of that column), and being surprised when they disagree.
Reasoning about JOINs from a memorized Venn diagram instead of asking "how many matches can each row have, and does this JOIN type preserve unmatched rows" — the habit that predicts row-count blowups from one-to-many joins before they happen.
Forgetting that SUM/AVG/MIN/MAX silently ignore NULL rather than treating it as zero, leading to a miscalculated AVG denominator.
Writing a SELECT list with a column that's neither aggregated nor in GROUP BY and being surprised the engine rejects it (or, worse, on a database that silently allows it, not realizing the returned value is arbitrary).

Key Takeaways

SQL has a written order (how you type it) and a logical execution order (FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT) — the logical order is what actually determines what a clause is allowed to reference and what it means, and internalizing it turns "why doesn't this work" into a mechanical, derivable answer instead of a rule to memorize.
Reason about JOINs by asking how many matches a row can have on the other side and whether the JOIN type guarantees unmatched rows survive — this predicts row-count inflation from one-to-many joins and explains INNER/LEFT/RIGHT/FULL/CROSS/SELF without needing a Venn diagram.
A condition on the optional side of a LEFT JOIN belongs in ON, not WHERE — putting it in WHERE silently turns the outer join back into an inner join, because WHERE runs after the join and discards NULL-filled unmatched rows that fail the condition.
UNION deduplicates and costs more; UNION ALL doesn't and is the right default unless duplicates are possible and unwanted. INTERSECT and EXCEPT are set intersection and (order-sensitive) set difference, distinct from what a JOIN does.
SQL's logic is three-valued (TRUE/FALSE/UNKNOWN), any comparison to NULL is UNKNOWN, and WHERE/HAVING only keep TRUE — this is the root cause of the classic NOT IN + NULL trap, where one stray NULL in a subquery silently zeroes out an entire result set; prefer NOT EXISTS when the subquery column's cleanliness isn't guaranteed.
GROUP BY collapses rows into buckets; HAVING filters those buckets after the collapse, which is exactly why it can reference aggregates and WHERE cannot — WHERE only ever sees individual rows, before grouping happens.
The fastest way to build real fluency is to trace queries step by step in logical order rather than reading them top to bottom — the worked example above is the habit to practice on every unfamiliar query before this track moves on to advanced-sql-window-functions-and-ctes, sql-query-optimization-and-indexing, and the full case-study-sql-interview-gauntlet.