Subqueries vs JOINs vs CTEs

A comparison of subqueries, JOINs, and common table expressions (CTEs). All three answer questions across multiple tables or aggregated steps – picking the right tool keeps queries fast and readable.

When a JOIN is enough

If you need columns from related tables in one result set, JOIN is usually the first choice. Clear, optimiser-friendly, easy to index.

SELECT c.name, o.placed_at, o.status
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Subqueries in WHERE

Use a subquery when you filter by an aggregate or a set built from another table:

-- Customers who placed at least one order in 2026
SELECT * FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE placed_at >= '2026-01-01'
);

-- Products priced above average
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

IN, EXISTS, and scalar subqueries (returning one value) each suit different shapes. EXISTS often performs well for “has related rows” checks:

SELECT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'paid'
);

Subqueries in FROM (derived tables)

Wrap a query as a inline view when you need to aggregate before joining:

SELECT c.name, totals.order_count, totals.revenue
FROM customers c
INNER JOIN (
  SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(
      (SELECT SUM(quantity * unit_price) FROM order_items oi WHERE oi.order_id = o.id)
    ) AS revenue
  FROM orders o
  GROUP BY customer_id
) AS totals ON totals.customer_id = c.id;

That nested scalar subquery inside SUM is hard to read – a CTE cleans it up.

Common table expressions (WITH)

CTEs name intermediate result sets. MySQL 8+ supports them. They read top to bottom like a script:

WITH line_totals AS (
  SELECT
    order_id,
    SUM(quantity * unit_price) AS order_total
  FROM order_items
  GROUP BY order_id
),
customer_stats AS (
  SELECT
    o.customer_id,
    COUNT(*) AS order_count,
    SUM(lt.order_total) AS revenue
  FROM orders o
  INNER JOIN line_totals lt ON lt.order_id = o.id
  GROUP BY o.customer_id
)
SELECT c.name, cs.order_count, cs.revenue
FROM customers c
INNER JOIN customer_stats cs ON cs.customer_id = c.id
ORDER BY cs.revenue DESC;

Same logic as nested derived tables, easier to debug – run each CTE alone during development.

Choosing between them

• JOIN – flat related data, one row per combination you need
• Subquery in WHERE – filter main rows by a condition from another table
• Derived table / CTE – multi-step logic, aggregate then join, reuse the same intermediate set twice
• CTE – prefer over nested subqueries when readability matters; performance is often similar

Anti-pattern: correlated subquery in SELECT

Running a subquery per output row can be slow at scale:

-- Works but can hurt on large tables
SELECT
  c.name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

Often rewrite as JOIN + GROUP BY or a CTE. EXPLAIN both if the report runs often.

Recursive CTEs (brief)

For tree structures (categories, org charts), MySQL 8+ recursive CTEs walk parent-child links. Niche but powerful – worth knowing they exist when adjacency lists outgrow self-joins.

The next tutorial covers transactions and isolation – keeping multi-step changes consistent when several queries must succeed or fail together.