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

NOT IN returns nothing if the subquery yields aNULL. Prefer NOT EXISTS for missing-match checks. A scalar subquery must return exactly one row, or the database errors.

Lesson

Joining Customers to Orders in SQL

Join two related tables on a key.

Lesson

Joining Orders to Products in SQL

Join through a bridge table.

Lesson

INNER JOIN vs LEFT JOIN in SQL

Choose whether unmatched rows stay or drop.

Lesson

SQL Self-Joins

Join a table to itself to compare its own rows.