Subqueries in SQL

A subquery is a query nested inside another query. It lets you use the result of one SELECT as an input to another — to filter, compare, or compute. Subqueries are how you answer layered questions like "which students are older than the average?" where you need one result (the average) to evaluate another (each student).

They can appear in the WHERE, FROM, or SELECT clause, and they come in a few distinct flavours.

Scalar subquery: returns a single value

A scalar subquery returns exactly one row and one column. You can use it anywhere a single value is expected:

-- PostgreSQL / MySQL compatible

-- Students older than the overall average age
SELECT full_name, age
FROM student
WHERE age > (SELECT AVG(age) FROM student);
--           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ scalar subquery: one number

The inner query computes the average once; the outer query compares each student to it. If a "scalar" subquery accidentally returns more than one row, the database raises an error — so make sure it truly yields one value.

Subquery with IN: membership

A subquery returning a list of values pairs with IN:

-- Students who are enrolled in at least one course
SELECT full_name
FROM student
WHERE student_id IN (SELECT student_id FROM enrolment);
--                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ list of student_ids

This keeps students whose ID appears anywhere in the enrolment table.

Be careful with NOT IN and NULLs: if the subquery's list contains any NULL, NOT IN returns no rows at all (a well-known SQL gotcha). Prefer NOT EXISTS for the "not present" case.

EXISTS: does a matching row exist?

EXISTS checks whether a subquery returns any row — it cares about existence, not values. It is often the clearest and most efficient way to ask "is there a related row?"

-- Students who have at least one enrolment, using EXISTS
SELECT s.full_name
FROM student s
WHERE EXISTS (
    SELECT 1
    FROM enrolment e
    WHERE e.student_id = s.student_id   -- correlated: refers to the outer row
);

The inner SELECT 1 is conventional — EXISTS ignores the selected columns and only asks "did any row come back?"

Correlated subqueries

The EXISTS example above is a correlated subquery: the inner query references a column from the outer query (s.student_id). That means it is evaluated once per outer row, not once overall. Correlated subqueries are powerful but can be slower — the database may run them repeatedly.

The opposite is a non-correlated (independent) subquery, like the AVG(age) example, which runs once and is reused.

-- Correlated: find students with NO enrolment (the "anti-join" pattern)
SELECT s.full_name
FROM student s
WHERE NOT EXISTS (
    SELECT 1 FROM enrolment e WHERE e.student_id = s.student_id
);

NOT EXISTS is the safe, NULL-proof way to find unmatched rows.

Subquery in the FROM clause (derived table)

A subquery in FROM acts as a temporary table — often called a derived table. You must give it an alias:

-- Count students per city, then keep only the busy cities
SELECT city, n
FROM (
    SELECT city, COUNT(*) AS n
    FROM student
    GROUP BY city
) AS city_counts          -- alias is mandatory
WHERE n > 5;

For complex cases, a Common Table Expression (CTE) with WITH is a more readable alternative to a FROM-clause subquery, and both PostgreSQL and MySQL 8+ support CTEs.

Subquery in the SELECT list

You can compute a per-row value with a scalar subquery in SELECT:

-- Show each student plus their enrolment count
SELECT
    s.full_name,
    (SELECT COUNT(*) FROM enrolment e WHERE e.student_id = s.student_id) AS courses
FROM student s;

This is correlated and runs per row. For many rows, a LEFT JOIN with GROUP BY is usually faster — which raises the key question below.

Subquery or JOIN?

Many subqueries can be rewritten as joins, and vice versa. Rough guidance:

• Use a JOIN when you need columns from both tables in the output.
• Use EXISTS / IN when you only need to test for a related row, not display its data.
• Use a scalar subquery for a single computed value used in a comparison.

Modern query optimizers often turn one into the other internally, so write whichever is clearest, then optimise only if measurement shows a problem.

Common mistakes

• NOT IN with NULLs. If the subquery can return NULL, NOT IN yields no rows. Use NOT EXISTS instead.
• Scalar subquery returning multiple rows. Used where one value is expected, it errors out. Add a LIMIT 1 or an aggregate, or fix the logic.
• Forgetting the alias on a FROM subquery. Derived tables must be named.
• Overusing correlated subqueries in SELECT. Running a subquery per row can be slow on large tables; a join often does the same job in one pass.
• Assuming subqueries are always slower than joins. Not true — EXISTS is frequently as fast or faster. Measure rather than guess.

FAQ

Can a subquery be inside another subquery? Yes, subqueries can nest several levels deep. Readability suffers quickly, though — CTEs (WITH) usually express deep nesting more clearly.

What is the difference between IN and EXISTS? IN compares a value against a list of returned values; EXISTS just checks whether any row exists. For "is there a match?" questions, EXISTS (and NOT EXISTS) handles NULLs more safely.

Are CTEs the same as subqueries? A CTE (WITH name AS (...)) is a named subquery you can reference, sometimes more than once. It is functionally similar but far more readable for complex queries.

Keep learning

Subqueries build on SQL SELECT basics, often replace or complement SQL joins, and frequently wrap aggregate functions.

Want to master when to reach for a subquery vs a join? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.