Correlated subqueries in SQL

A correlated subquery is a subquery that refers to a column of the outer query, such as q.category = p.category, where p is the outer table. Because that value changes from row to row, the subquery is, logically, evaluated again for every outer row, with that row’s values filled in. That makes it the natural way to compare each row with its own group: products dearer than the average of their own category, each customer’s latest order, the students whose best offer beats last year’s. It reads almost like the question in plain words. The price is speed: run once per row, the subquery needs an index on the column it filters by, or a rewrite as a join or a window function, both of which this lesson shows.

All of the examples run in the page itself, with sql.js 1.14.2 running SQLite 3.49.1 inside the browser tab; statements about PostgreSQL 18 and MySQL 8.4 rest on their manuals.

A subquery that reads the outer row

-- Nine products of a grocery shop, prices in paise. Spices are cheap, so they rarely beat the shop's average.
CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL
);
INSERT INTO products VALUES
  (1, 'Masala tea 250 g',    'drinks', 18000), (2, 'Filter coffee 500 g', 'drinks', 42000),
  (3, 'Green tea 100 g',     'drinks', 24000), (4, 'Basmati rice 5 kg',   'grains', 65000),
  (5, 'Wheat atta 10 kg',    'grains', 48000), (6, 'Red lentils 1 kg',    'grains', 14000),
  (7, 'Turmeric 100 g',      'spices',  4000), (8, 'Chilli powder 200 g', 'spices',  6000),
  (9, 'Cumin seeds 100 g',   'spices', 11000);

-- 1. Uncorrelated: one average for the whole shop, computed once.
SELECT name, category, price_paise / 100.0 AS rupees
FROM products
WHERE price_paise > (SELECT avg(price_paise) FROM products)
ORDER BY category, name;

-- 2. Correlated: p.category inside the subquery is the outer row's category, so each product meets the average of
--    its own category.
SELECT p.name, p.category, p.price_paise / 100.0 AS rupees,
       round((SELECT avg(q.price_paise) FROM products AS q WHERE q.category = p.category) / 100.0, 2) AS category_average
FROM products AS p
WHERE p.price_paise > (SELECT avg(q.price_paise) FROM products AS q WHERE q.category = p.category)
ORDER BY p.category, p.name;

┌─────────────────────┬──────────┬────────┐
│        name         │ category │ rupees │
├─────────────────────┼──────────┼────────┤
│ Filter coffee 500 g │ drinks   │ 420.0  │
│ Basmati rice 5 kg   │ grains   │ 650.0  │
│ Wheat atta 10 kg    │ grains   │ 480.0  │
└─────────────────────┴──────────┴────────┘
┌─────────────────────┬──────────┬────────┬──────────────────┐
│        name         │ category │ rupees │ category_average │
├─────────────────────┼──────────┼────────┼──────────────────┤
│ Filter coffee 500 g │ drinks   │ 420.0  │ 280.0            │
│ Basmati rice 5 kg   │ grains   │ 650.0  │ 423.33           │
│ Wheat atta 10 kg    │ grains   │ 480.0  │ 423.33           │
│ Cumin seeds 100 g   │ spices   │ 110.0  │ 70.0             │
└─────────────────────┴──────────┴────────┴──────────────────┘

The first query compares every product with one number, the shop’s average of 257.78 rupees, and no spice comes close. The second gives each product its own yardstick. Its subquery mentions p.category, a column of the outer row, so for cumin seeds it computes the average of the spices, 70 rupees, and for green tea the average of the drinks, 280 rupees. Cumin seeds at 110 rupees now pass, while green tea at 240 does not. SQLite’s manual describes the rule: a correlated subquery is re-evaluated each time its result is required, while an uncorrelated one is evaluated once and reused. PostgreSQL’s manual says the outer values act as constants during any one evaluation.

Give the outer and inner tables different aliases, p and q here. When the same name could mean either, the engine resolves it to the nearest query, the subquery itself; MySQL’s manual calls this evaluating from inside to outside. A missing alias can silently turn the correlation into category = category inside the subquery, which compares each inner row with itself and is true for every row that has a category.

The latest order of each customer

“The latest row per group” is the classic job for a correlated subquery:

-- Orders of four customers. Bilal placed two orders on the same day.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Asha',   '2026-03-02', 50000), (2, 'Asha',   '2026-03-09', 70000), (3, 'Bilal',  '2026-03-05', 30000),
  (4, 'Bilal',  '2026-03-05', 45000), (5, 'Chitra', '2026-03-01', 90000), (6, 'Chitra', '2026-02-20', 20000),
  (7, 'Dev',    '2026-03-11', 25000);

-- The latest order of each customer: keep an order whose date is the latest date of the same customer.
SELECT o.id, o.customer, o.placed_on, o.amount_paise / 100.0 AS rupees
FROM orders AS o
WHERE o.placed_on = (SELECT max(o2.placed_on) FROM orders AS o2 WHERE o2.customer = o.customer)
ORDER BY o.customer, o.id;

┌────┬──────────┬────────────┬────────┐
│ id │ customer │ placed_on  │ rupees │
├────┼──────────┼────────────┼────────┤
│ 2  │ Asha     │ 2026-03-09 │ 700.0  │
│ 3  │ Bilal    │ 2026-03-05 │ 300.0  │
│ 4  │ Bilal    │ 2026-03-05 │ 450.0  │
│ 5  │ Chitra   │ 2026-03-01 │ 900.0  │
│ 7  │ Dev      │ 2026-03-11 │ 250.0  │
└────┴──────────┴────────────┴────────┘

For each order, the subquery finds the latest date of the same customer, and the order is kept when its date equals it. Asha, Chitra and Dev get one order each. Bilal gets two, because both of his orders were placed on the latest day. Whether that is right depends on the question: “every order placed on the customer’s last day” wants both, “one row per customer” needs a rule that breaks the tie.

Three ways to ask a group-wise question

All three return the same rows on clean data, and they differ in how they break ties, how missing values behave and how much work they cost:

• A tie-breaker makes the correlated form return exactly one row per group.
• A NULL in the date makes the forms disagree about the latest row.
• Without an index a correlated subquery rereads the table once per outer row, which grows with the square of the table.
• A window function ranks the rows of each group in one pass.

Your turn

-- For every student, the offer(s) with the highest salary: student, company, salary_rupees.
SELECT student, company, max(salary_rupees) AS salary_rupees
FROM offers
GROUP BY student;

# Sample tests: each one builds a small offers table, runs your query on it and compares the rows it returns (in any
# order).
tests:
  - name: picks each student's highest offer
    setup: |
      CREATE TABLE offers (id INTEGER PRIMARY KEY, student TEXT NOT NULL, company TEXT NOT NULL, salary_rupees INTEGER NOT NULL);
      INSERT INTO offers VALUES
        (1, 'Ananya', 'Banyan Analytics', 900000), (2, 'Ananya', 'Bluefin Data', 1200000),
        (3, 'Rohan', 'Kite Systems', 650000), (4, 'Meher', 'Bluefin Data', 800000), (5, 'Meher', 'Orbit Retail', 700000);
    expected:
      columns: [student, company, salary_rupees]
      rows:
        - ['Ananya', 'Bluefin Data', 1200000]
        - ['Rohan', 'Kite Systems', 650000]
        - ['Meher', 'Bluefin Data', 800000]
  - name: keeps both offers when two share the highest salary
    setup: |
      CREATE TABLE offers (id INTEGER PRIMARY KEY, student TEXT NOT NULL, company TEXT NOT NULL, salary_rupees INTEGER NOT NULL);
      INSERT INTO offers VALUES
        (1, 'Ishaan', 'Kite Systems', 1000000), (2, 'Ishaan', 'Orbit Retail', 1000000), (3, 'Ishaan', 'Banyan Analytics', 750000),
        (4, 'Zara', 'Bluefin Data', 900000);
    expected:
      columns: [student, company, salary_rupees]
      rows:
        - ['Ishaan', 'Kite Systems', 1000000]
        - ['Ishaan', 'Orbit Retail', 1000000]
        - ['Zara', 'Bluefin Data', 900000]
  - name: compares each student only with their own offers
    setup: |
      CREATE TABLE offers (id INTEGER PRIMARY KEY, student TEXT NOT NULL, company TEXT NOT NULL, salary_rupees INTEGER NOT NULL);
      INSERT INTO offers VALUES
        (1, 'Kavya', 'Orbit Retail', 500000), (2, 'Kavya', 'Kite Systems', 450000), (3, 'Vikram', 'Banyan Analytics', 1500000);
    expected:
      columns: [student, company, salary_rupees]
      rows:
        - ['Kavya', 'Orbit Retail', 500000]
        - ['Vikram', 'Banyan Analytics', 1500000]

The latest row per group four ways, checked against each other Pro: opens with a Pro, Premium or Ultimate pass

How often a correlated subquery runs, measured on 3,000 orders Pro: opens with a Pro, Premium or Ultimate pass

Rewriting correlated subqueries as joins, window functions and updates Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• A correlated subquery refers to the outer row, and logically runs again for every outer row with that row’s values.
• It answers group-wise questions directly, such as each row against its own category, or the latest row of each group, where ties return several rows.
• Its cost grows with the number of outer rows times the work of each run, so it needs an index on the column it correlates on, or a rewrite as a join or a window function.
• Give the outer and inner tables different aliases, so that the correlation means what you intend.