SQLite Correlated Subqueries

A correlated subquery references columns from the outer query. Unlike a regular subquery that runs once, a correlated subquery runs once for each row processed by the outer query. This makes it powerful for row-by-row comparisons but slower on large tables.

Regular vs Correlated Subquery

REGULAR SUBQUERY: runs once, result is fixed
──────────────────────────────────────────────────────
SELECT name FROM products
WHERE price > (SELECT AVG(price) FROM products);
               ↑ runs once → returns 14.49
               same for every outer row

CORRELATED SUBQUERY: runs once per outer row
──────────────────────────────────────────────────────
SELECT name, price FROM products p
WHERE price > (
  SELECT AVG(price) FROM products
  WHERE category = p.category   ← references outer query's row
);                               ↑ changes with each outer row

Sample Tables

products
──────────────────────────────────────────────────
id │ name          │ category    │ price
───┼───────────────┼─────────────┼───────
1  │ Notebook      │ Stationery  │ 2.99
2  │ Pen           │ Stationery  │ 0.99
3  │ Ruler         │ Stationery  │ 1.49
4  │ Backpack      │ Bags        │ 24.99
5  │ Laptop Bag    │ Bags        │ 34.99
6  │ Tote Bag      │ Bags        │ 12.00
7  │ Water Bottle  │ Accessories │ 8.50
8  │ Phone Stand   │ Accessories │ 15.00

Products Priced Above Their Category Average

SELECT p.name, p.category, p.price
FROM products p
WHERE p.price > (
  SELECT AVG(price)
  FROM products
  WHERE category = p.category   ← correlated: uses outer p.category
)
ORDER BY p.category;

How it works row by row:
─────────────────────────────────────────────────────────────────
Row: Notebook  (Stationery)
  Inner query: AVG(price) WHERE category='Stationery' → 1.82
  Is 2.99 > 1.82? YES → include

Row: Pen  (Stationery)
  Inner query: AVG(price) WHERE category='Stationery' → 1.82
  Is 0.99 > 1.82? NO → exclude

Row: Backpack  (Bags)
  Inner query: AVG(price) WHERE category='Bags' → 23.99
  Is 24.99 > 23.99? YES → include

Final result:
name        │ category    │ price
────────────┼─────────────┼───────
Notebook    │ Stationery  │ 2.99
Backpack    │ Bags        │ 24.99
Laptop Bag  │ Bags        │ 34.99
Phone Stand │ Accessories │ 15.00

Correlated Subquery with EXISTS

employees
──────────────────────────────────────
id │ name  │ dept_id │ salary
───┼───────┼─────────┼────────
1  │ Alice │ 10      │ 85000
2  │ Bob   │ 20      │ 62000
3  │ Carol │ 10      │ 91000
4  │ Dan   │ 30      │ 55000

-- Find employees who earn more than any colleague in dept 20
SELECT name, salary
FROM employees e1
WHERE EXISTS (
  SELECT 1
  FROM employees e2
  WHERE e2.dept_id = 20
    AND e1.salary > e2.salary
);

For each outer row, the inner query checks:
  "Is there any dept-20 employee who earns less than me?"

Alice (85000) > Bob (62000) → YES → include
Bob   (62000) > nobody in dept 20  → NO → exclude
Carol (91000) > Bob (62000) → YES → include
Dan   (55000) > nobody in dept 20  → NO → exclude

name  │ salary
──────┼────────
Alice │ 85000
Carol │ 91000

Correlated Subquery to Find Maximums Per Group

-- Most expensive product in each category
SELECT name, category, price
FROM products p
WHERE price = (
  SELECT MAX(price)
  FROM products
  WHERE category = p.category
)
ORDER BY category;

name          │ category    │ price
──────────────┼─────────────┼───────
Phone Stand   │ Accessories │ 15.00
Laptop Bag    │ Bags        │ 34.99
Notebook      │ Stationery  │ 2.99

Correlated Subquery vs JOIN Performance

CORRELATED SUBQUERY: runs inner query once per outer row
  100 outer rows × inner scan = potentially 100 inner scans
  Slower on large tables

EQUIVALENT JOIN (often faster):
──────────────────────────────────────────────────────────
SELECT p.name, p.category, p.price
FROM products p
JOIN (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) cat_avg ON p.category = cat_avg.category
WHERE p.price > cat_avg.avg_price;

The derived-table JOIN pre-computes averages once, not per row.

When to Use Correlated Subqueries

GOOD USE CASES:
──────────────────────────────────────────────────
✔ EXISTS / NOT EXISTS checks
✔ Per-row maximum or minimum lookups
✔ When logic reads more clearly than an equivalent JOIN
✔ Small to medium datasets where performance is acceptable

AVOID WHEN:
──────────────────────────────────────────────────
✘ Table has millions of rows (use JOIN instead)
✘ Inner query does a full table scan each time

Key Takeaways

A correlated subquery references a column from the outer query
It executes once per row of the outer query, unlike a regular subquery which executes once
Common uses: per-group comparisons, EXISTS checks, row-by-row max/min lookups
On large tables, a JOIN with a derived table is usually faster
The outer table alias (e.g. p) must be referenced inside the subquery for correlation

Previous lesson

Back to course

Next lesson