Cheatsheet

SQL Subqueries & CTEs Cheatsheet

Scalar, IN, and EXISTS subqueries plus the WITH clause.

Which form fits

Form	Use it when
Scalar subquery	You need one value (an average, a max).
`IN` subquery	You need a set of allowed values.
`EXISTS`	You only care if a related row exists.
`WITH` / CTE	The step deserves a name or gets reused.

Scalar: one value

Compare each row to an aggregate

select product_name, price
from products
where price > (select avg(price) from products)

IN: a set from another query

Filter by keys from another table

select product_name
from products
where supplier_id in (
  select supplier_id from suppliers where state = 'OR'
)

EXISTS: does a match exist?

Customers who have ordered

select c.first_name, c.last_name
from customers as c
where exists (
  select 1 from orders as o where o.customer_id = c.customer_id
)

NOT EXISTS keeps rows with no match. This is the safe way to ask "who has never ordered?".

CTE: name the step

WITH reads top to bottom

with order_revenue as (
  select o.customer_id,
         sum(oi.quantity * oi.unit_price) as revenue
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
  group by o.customer_id
)
select customer_id, revenue
from order_revenue
order by revenue desc, customer_id
limit 5

Watch out

A nullable value in a NOT IN subquery can make otherwise non-matching rows evaluate to UNKNOWN; prefer NOT EXISTS for missing-match checks. A DuckDB scalar subquery may return zero rows (which becomes NULL) or one row. More than one row raises an error by default.

Lesson

Joining Customers to Orders in SQL

Join two related tables on a key.

Reference

SQL JOIN Types

INNER, LEFT, RIGHT, FULL, and CROSS joins.

Interview

SQL JOIN Interview Questions

Practice join keys, left joins, duplicate rows, and debugging.

Mistake

COUNT or SUM Too High After a JOIN

One-to-many joins can inflate totals.