SQL Roadmap/Joins & Multi-Table Queries

Set Operations & Anti-Joins

UNION/UNION ALL/INTERSECT/EXCEPT combine or subtract entire result sets rather than matching rows side-by-side — and the anti-join ("rows in A with no match in B") hides a genuinely dangerous NULL trap in its most common form, NOT IN.

!!3/5Theory: 25m3 problems

Set operations: combining whole result sets, not rows

UNION, INTERSECT, and EXCEPT (called MINUS in Oracle) operate on entire query results as sets, stacking or subtracting rows rather than matching them column-by-column across tables the way a JOIN does. All three require both queries to select the same number of columns with compatible types, in the same order.

SELECT customer_id FROM online_orders UNION SELECT customer_id FROM store_orders; -- every customer who ordered online OR in-store, each appearing once

UNION removes duplicate rows from the combined result by default — this is a real cost on large result sets, since it implies a sort or hash pass purely to dedupe. UNION ALL skips that dedup step and keeps every row from both queries, including duplicates. Default to UNION ALL unless you specifically need deduplication — it's faster, and using plain UNION "just in case" when you don't actually expect (or want to hide) duplicates is a habit worth breaking.

INTERSECT returns only rows present in both result sets; EXCEPT/MINUS returns rows in the first result set that are not present in the second. Both are less commonly available or performant than the equivalent JOIN/WHERE EXISTS rewrite, but they read very cleanly for "what changed between these two snapshots" style questions.

Anti-joins: three ways to say "rows in A with no match in B"

This is the single most important pattern in this subtopic, because there are three ways to write it and only two of them are safe in the presence of NULLs.

1. LEFT JOIN + IS NULL (always safe):

SELECT c.customer_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL;

2. NOT EXISTS (always safe, and usually the most readable):

SELECT c.customer_id FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );

3. NOT IN (dangerous — silently broken the moment the subquery can return a NULL):

SELECT customer_id FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders); -- looks identical to #2!

Here's exactly why #3 breaks: NOT IN (v1, v2, NULL) expands to col != v1 AND col != v2 AND col != NULL. That last comparison is UNKNOWN (not TRUE, not FALSE — recall the NULL-comparison discussion from Filtering & Sorting Basics), and AND with any UNKNOWN operand can never evaluate to TRUE. The practical consequence: if the orders.customer_id column returned by the subquery contains even a single NULL (perhaps a walk-in order with no customer attached), the entire NOT IN query returns zero rows — not "zero rows because everyone ordered," but zero rows unconditionally, silently, with no error. This is one of the most common real-world SQL bugs, precisely because it works perfectly in testing (where the sample data happens to have no NULLs) and breaks in production the day it doesn't.

The fix, if you must use NOT IN, is to filter the subquery explicitly: NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL). But the better answer in an interview is simply: prefer NOT EXISTS or LEFT JOIN ... IS NULL for anti-joins, full stop, and say why.

Why interviewers care

This is a rare case where a genuinely senior signal is a candidate volunteering the NULL caveat before being asked — "I'll use NOT EXISTS here rather than NOT IN, since NOT IN breaks silently if that column is ever nullable" is a sentence that immediately distinguishes someone who has been burned by this in production from someone reciting syntax. It's also a favorite "gotcha" follow-up: an interviewer who sees you write NOT IN may deliberately ask "what happens if a row in the subquery has a NULL customer_id?" to see if you know.

Pitfalls and interview gotchas

  • Reaching for NOT IN against a subquery without checking (or filtering out) NULLs. The core lesson of this subtopic.
  • Using UNION when UNION ALL would do, paying an unnecessary dedup cost.
  • Mismatched column counts or incompatible types between the two sides of a set operation — a purely mechanical error, but one that's easy to make when a query gets edited over time and one side gains a column the other didn't.
  • Forgetting that INTERSECT/EXCEPT compare entire rows, not a single key column, unless you explicitly SELECT only the key column on both sides.

Where this goes next

Aggregation & Grouping (next) shifts from combining/filtering row sets to summarizing them — GROUP BY, HAVING, and the aggregate functions that turn many rows into one summary row per group, which is where most "count/sum/average X per Y" interview questions live.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked

Optional Practice (Extra Reps)

For once you've cleared the main set above and want more reps on this pattern. These don't count toward the roadmap's progress stats — solve them purely for your own benefit.