What is a subquery and what is a correlated subquery?

Q: What is a subquery and what is a correlated subquery?

**Target Audience:** Beginners who have written a SELECT with a WHERE clause and now meet one SELECT nested inside another; also anyone preparing for the follow-up questions on performance. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Notes on MySQL and SQL Server are marked as such. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Imagine you are a manager with a spreadsheet of salaries, and your boss asks two questions. The first: *"Who earns more than the company average?"* You work out the average once (81,125), write it on a sticky note, then go down the list

The problem

Question presented to candidate: "You will see queries with a SELECT nested inside another SELECT. What is a subquery, what makes a subquery correlated, and how does that change how the database runs it? Show me one of each."

What a strong answer should cover:

A subquery is a complete SELECT nested inside another statement, in WHERE, in the SELECT list, in FROM (a derived table) or in HAVING
The shapes: scalar (one value), row, column list for IN, and table (a derived table in FROM)
A non-correlated subquery does not reference the outer query, so it can be evaluated once (PostgreSQL shows it as an InitPlan)
A correlated subquery references a column of the outer row, so logically it runs once per outer row (PostgreSQL shows a SubPlan with loops equal to the outer row count)
Trade-offs: correlated subqueries are clear but can be slow without an index on the correlation column; rewrites with a JOIN plus GROUP BY, a window function, or LATERAL
Engine detail: a scalar subquery that returns two rows is an error in PostgreSQL but silently uses the first row in SQLite

Code / implementation expected: Yes — one plain subquery (above the company average) and one correlated subquery (above the own-department average).

Schema used by the examples:

subquery

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have written a SELECT with a WHERE clause and now meet one SELECT nested inside another; also anyone preparing for the follow-up questions on performance. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Notes on MySQL and SQL Server are marked as such.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Imagine you are a manager with a spreadsheet of salaries, and your boss asks two questions.

The first: *"Who earns more than the company

The solution is hidden so you can try first

15 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLPlain (non-correlated) subquery: employees paid above the company average

SQLCorrelated subquery: employees paid above the average of their own department

SQLSQLite query plans: SCALAR SUBQUERY for the plain one, CORRELATED SCALAR SUBQUERY for the other

PostgreSQL: the correlated SubPlan runs once per outer row (loops=8)

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT e.name, e.salary
FROM employees AS e
WHERE e.salary > (
  SELECT AVG(i.salary) FROM employees AS i WHERE i.dept_id = e.dept_id
);

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.