SQL Subqueries Explained: Nested SELECT Statements

A subquery is a SELECT statement nested inside another query, wrapped in parentheses. The inner query runs first (conceptually), and its result feeds into the outer query — as a single value, a list, or an entire virtual table. They're most useful when a JOIN would be awkward or when you only need an aggregate result to compare against, not the joined rows themselves.

Scalar subqueries — a single value

A scalar subquery returns exactly one row, one column — a single value you can use anywhere an expression is allowed, including right inside a comparison:

-- Employees earning more than the company average
SELECT first_name, salary
FROM Employees
WHERE salary > (
    SELECT AVG(salary) FROM Employees
);

The inner query computes one number (the average salary across the whole table); the outer query then compares every employee's salary against that single number.

Subqueries with IN — a list of values

When the inner query returns multiple rows of a single column, pair it with IN (or NOT IN):

-- Products that have never been in a shipped order
SELECT product_id, name
FROM Products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id FROM Orders WHERE status = 'Shipped'
);

Watch out: if the inner query's column can contain NULL, NOT IN can silently return zero rows for the whole query, because comparing anything to NULL is neither true nor false. Every major database does this, SQLite and therefore this playground included; the EXISTS vs IN guide shows it happening on the sample data. If NULLs are possible, filter them out inside the subquery (WHERE product_id IS NOT NULL) or use NOT EXISTS instead.

EXISTS — does at least one row match?

EXISTS doesn't care what the subquery returns, only whether it returns any row at all. You will hear that this makes it faster than IN, since it can stop at the first match, but modern query planners usually turn both into the same plan. Choose between them for readability and for how they treat NULLs, which the EXISTS vs IN guide covers in detail.

-- Customers who have placed at least one order
SELECT company
FROM Customers c
WHERE EXISTS (
    SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id
);

This is a correlated subquery — notice it references c.customer_id from the outer query. Unlike the earlier examples, this inner query can't run on its own; it re-evaluates once per outer row.

Subqueries in FROM — a virtual table

A subquery can also stand in for a table, letting you query the results of another query:

SELECT dept_id, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM Employees
    GROUP BY dept_id
) dept_averages
WHERE avg_salary > 90000;

Here the inner query pre-aggregates by department, and the outer query filters those already-computed averages — something you couldn't do directly with WHERE (see our GROUP BY vs HAVING guide for why).

Correlated subqueries — one run per row

Every subquery so far could run on its own. A correlated subquery cannot: it references a column from the outer query, so it has to be evaluated once for each row the outer query considers. That is what makes it powerful, and also why it can be slow on a large table, especially when the column it matches on has no index.

This finds everyone paid above the average for their own department — a question a single plain aggregate cannot answer, because the comparison value changes from row to row:

SELECT e.first_name, e.dept_id, e.salary
FROM Employees e
WHERE e.salary > (SELECT AVG(salary) FROM Employees x WHERE x.dept_id = e.dept_id);

The alias e on the outside and x on the inside are doing essential work here. Inside the subquery, x.dept_id means the row being averaged and e.dept_id means the row the outer query is currently testing. Without distinct aliases there would be no way to say which is which.

Subqueries in the SELECT list

A subquery that returns exactly one value can sit in the SELECT list and become a column of its own. This is an easy way to attach a per-row summary without changing the shape of the main query:

SELECT c.company,
       (SELECT COUNT(*) FROM Orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM Customers c
ORDER BY order_count DESC;

It must return one column and at most one row (no row at all gives NULL). If it returns several rows, PostgreSQL, MySQL, SQL Server and Oracle stop with an error. SQLite, which runs this playground, does not: it quietly uses the first row it finds, so WHERE salary = (SELECT salary FROM Employees WHERE dept_id = 10) returns Ananya alone here instead of failing. The fix is the same everywhere: make sure the subquery can only produce one row, or compare with IN. For the order counts above, a LEFT JOIN with GROUP BY gives the same answer and can run faster over large tables, but this form is often clearer when you only need one such figure.

Subquery vs JOIN: which one?

Both often solve the same problem. General rule of thumb:

• Need columns from both tables in the result? Use a JOIN.
• Only need to filter or compare against a value/list computed from another table? A subquery is usually more readable.
• Checking existence without needing any data from the other table? EXISTS reads more clearly than a JOIN + DISTINCT.

Practice this topic

Reading explains it; solving it is what makes it stick. These exercises use exactly what this page covered, and your answer is checked by running it against the same database:

• Better paid than average Medium
• Customers with a shipped order Medium
• Customers still waiting Medium

See all 42 exercises →

Frequently asked questions

Related reading

• EXISTS vs IN in SQL: Which to Use and When
• How to Find the Second Highest Salary in SQL (5 Ways)
• SQL JOINs Explained: INNER, LEFT, RIGHT & FULL
• 25 SQL Interview Questions and Answers
• SQL CTEs Explained: How the WITH Clause Works