Subqueries

What you will be able to do

• Filter with IN and EXISTS
• Use a scalar subquery in SELECT
• Prefer joins or CTEs when readability wins

Why this matters for data work

Subqueries express “rows that match another question” — customers above average spend, skus never sold, stores with returns. Overused nesting becomes unreadable; CTEs (next) help.

Concepts

• WHERE customer_id IN (SELECT …).
• WHERE EXISTS (SELECT 1 FROM … WHERE …).
• Scalar: (SELECT avg(line_total) FROM orders) in SELECT list.
• Correlated subquery — references outer row; can be slow; know it exists.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
SELECT order_id, line_total
FROM orders
WHERE line_total > (SELECT avg(line_total) FROM orders WHERE status = 'completed')
  AND status = 'completed'
ORDER BY line_total DESC
LIMIT 10;

SELECT DISTINCT customer_id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM orders x
  WHERE x.customer_id = o.customer_id AND x.status = 'returned'
)
ORDER BY 1
LIMIT 10;

SELECT sku FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.sku = p.sku);
SQL

What you should see

• Completed orders above average line total.
• Some customers with at least one return.
• Empty “never sold” list on this seed (all skus appear) — still a valid pattern.

Common mistakes

• IN with NULL — NULL in the list can empty the result; prefer EXISTS.
• Deep nesting — rewrite as join/CTE for teammates.

Next

CTEs