SQL mental model: grain, order, nulls

1LOGICAL PROCESSING ORDER (conceptual; dialects vary on details)23  1. FROM / JOIN     build the working row set (including ON filters)4  2. WHERE           filter rows (no SELECT aliases; no aggregates yet)5  3. GROUP BY        collapse to groups6  4. HAVING          filter groups (aggregates OK)7  5. SELECT          compute expressions, aliases, DISTINCT8  6. ORDER BY        sort (SELECT aliases usually OK)9  7. LIMIT / OFFSET  truncate1011Windows (OVER ...) are evaluated after SELECT list inputs are known12in most mental models - after grouping, before final ORDER BY for display.13CTEs/subqueries run as nested FROM sources: each has its own ladder.1415Interview line: "WHERE cannot use SELECT aliases; HAVING can use aggregates."

1-- BAD: revenue by user, but line items fan out orders2SELECT u.user_id, SUM(o.amount) AS revenue3FROM users u4JOIN orders o ON o.user_id = u.user_id5JOIN order_items i ON i.order_id = o.order_id   -- multiplies order rows6GROUP BY u.user_id;7-- SUM(o.amount) is now wrong: amount repeated per line item89-- GOOD: aggregate to the grain you need, then join10WITH order_revenue AS (11  SELECT order_id, user_id, amount12  FROM orders13),14items_per_order AS (15  SELECT order_id, COUNT(*) AS n_items16  FROM order_items17  GROUP BY order_id18)19SELECT o.user_id, SUM(o.amount) AS revenue, SUM(i.n_items) AS items20FROM order_revenue o21LEFT JOIN items_per_order i ON i.order_id = o.order_id22GROUP BY o.user_id;

1-- COUNT variants interviewers love2SELECT3  COUNT(*)              AS n_rows,          -- all rows, incl. null-heavy ones4  COUNT(email)          AS n_email_present, -- non-null email only5  COUNT(DISTINCT email) AS n_unique_email   -- distinct non-null emails6FROM users;78-- COUNT(col) ignores NULL; COUNT(*) does not.9-- COUNT(DISTINCT col) also ignores NULL in standard SQL.

1-- Three-valued logic demo2-- NULL = NULL      → UNKNOWN (not TRUE)3-- NULL <> 1        → UNKNOWN4-- WHERE drops UNKNOWN the same as FALSE56SELECT * FROM t WHERE nullable_col = NULL;      -- always empty7SELECT * FROM t WHERE nullable_col IS NULL;     -- correct89-- NOT IN bomb: if subquery returns a NULL, NOT IN is never TRUE10SELECT *11FROM users u12WHERE u.id NOT IN (SELECT user_id FROM bans);  -- dangerous if user_id can be NULL1314-- Safer anti-join patterns15SELECT u.*16FROM users u17LEFT JOIN bans b ON b.user_id = u.id18WHERE b.user_id IS NULL;1920-- or NOT EXISTS (preferred by many seniors)21SELECT u.*22FROM users u23WHERE NOT EXISTS (SELECT 1 FROM bans b WHERE b.user_id = u.id);

1-- Correlated: users with above-average order count in their own country2SELECT u.user_id, u.country3FROM users u4WHERE (5  SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id6) > (7  SELECT AVG(cnt) FROM (8    SELECT COUNT(*) AS cnt9    FROM orders o210    JOIN users u2 ON u2.user_id = o2.user_id11    WHERE u2.country = u.country12    GROUP BY o2.user_id13  ) s14);1516-- Often clearer: pre-aggregate, then join thresholds17WITH per_user AS (18  SELECT u.user_id, u.country, COUNT(o.order_id) AS n_orders19  FROM users u20  LEFT JOIN orders o ON o.user_id = u.user_id21  GROUP BY u.user_id, u.country22),23country_avg AS (24  SELECT country, AVG(n_orders) AS avg_orders25  FROM per_user26  GROUP BY country27)28SELECT p.user_id, p.country, p.n_orders29FROM per_user p30JOIN country_avg c ON c.country = p.country31WHERE p.n_orders > c.avg_orders;