Subqueries and CTEs

Subqueries and CTEs: Reading Nested Logic

The one idea: Real dashboard queries are queries stacked inside queries, and the way to read them is to state, for each step, what one row represents. The trap to memorize: a "not in" filter returns zero rows the moment the inner list contains a missing value — which looks like a real answer and fools everyone.

⚠️ The trap: WHERE customer_id NOT IN (SELECT customer_id FROM orders) gives me the customers who have never ordered. That feels right and it's wrong. If even one row in that subquery is NULL, NOT IN returns zero rows every single time — a plausible-looking "nobody" that's really a broken query; use NOT EXISTS instead.

Real dashboard queries are almost never one SELECT. They're a query inside a query, or three named steps chained together. Nesting is where readers lose the thread — and where one of the most damaging silent bugs in all of SQL lives (a NOT IN that returns zero rows and looks like "no results" instead of "broken query"). This lesson gives you a reading strategy for nested logic and the two traps worth memorizing.

Subquery in WHERE: a filter built from another query

SELECT customer_id, order_total
FROM orders
WHERE customer_id IN (SELECT customer_id FROM churned_customers)

Read the inner query first, as if it ran alone: "give me the list of churned customer IDs." Then the outer query: "keep orders whose customer is on that list." A subquery in WHERE is just a filter whose values come from data instead of being typed in by hand.

The EXISTS variant asks a yes/no question per row:

SELECT…