SQL Subqueries Explained: Correlated vs Non-Correlated, With Worked Examples

Subqueries are usually the point in a SQL course where students who felt confident with basic SELECT statements suddenly hit a wall. The syntax of “a query inside a query” isn’t the hard part — it’s knowing where a subquery can go (WHERE, FROM, or SELECT), understanding the difference between one that runs once and one that re-runs for every row, and recognizing when a subquery is actually the wrong tool and a JOIN would be simpler. This guide works through all of that with fully worked examples, using a consistent sample database throughout so you can see exactly how each version behaves.

The Sample Tables Used Throughout This Guide

employees
+----+------------+------------+--------+------------+
| id | name       | dept_id    | salary | hire_date  |
+----+------------+------------+--------+------------+
| 1  | Alice      | 10         | 72000  | 2021-03-01 |
| 2  | Ben        | 10         | 65000  | 2022-06-15 |
| 3  | Chidi       | 20        | 58000  | 2020-01-10 |
| 4  | Dana       | 20         | 91000  | 2019-11-20 |
| 5  | Eshe       | 30         | 60000  | 2023-02-05 |
+----+------------+------------+--------+------------+

departments
+---------+-------------+
| dept_id | dept_name   |
+---------+-------------+
| 10      | Engineering |
| 20      | Sales       |
| 30      | Marketing   |
+---------+-------------+

What a Subquery Actually Is

A subquery is simply a SELECT statement nested inside another SQL statement, enclosed in parentheses. The outer query uses the subquery’s result as an input — a single value, a list of values, or an entire table, depending on where the subquery is placed.

The three places a subquery can appear:

1. In the WHERE clause — filters rows based on a comparison to the subquery’s result
2. In the FROM clause — treats the subquery’s result as a temporary table to query further
3. In the SELECT clause — computes a value for each row using a subquery

Recognizing which of these three a specific assignment question is asking for is often the first hurdle, since each has slightly different syntax rules.

Subqueries in the WHERE Clause: The Most Common Type

Worked example 1 — a subquery returning a single value:

Problem: Find all employees earning more than the company’s average salary.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

How this executes: The inner query (SELECT AVG(salary) FROM employees) runs first, producing a single number (the company-wide average, 69,200). The outer query then becomes, effectively, WHERE salary > 69200. This is called a scalar subquery because it returns exactly one value.

Common assignment mistake: Forgetting that a scalar subquery must return exactly one row and one column. If the inner query accidentally returns multiple rows (for example, if you forgot the AVG() and just selected salary), the database will raise an error, since a > comparison can’t be made against a whole list of values.

Worked example 2 — a subquery returning a list of values (using IN):

Problem: Find all employees who work in a department based in a city called “Austin” (assume a separate offices table maps dept_id to city).

SELECT name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM offices WHERE city = 'Austin');

How this executes: The inner query returns a list of department IDs (potentially more than one, if several departments are based in Austin), and the outer query checks whether each employee’s dept_id appears anywhere in that list — this is exactly why IN is used here instead of =, since = only works when comparing to a single value.

Common assignment mistake: Using = instead of IN when the subquery could plausibly return more than one row. This works fine when the subquery happens to return exactly one row during testing, but breaks (with a runtime error) the moment the underlying data changes and the subquery starts returning multiple rows — a fragile pattern that instructors specifically watch for.

Correlated Subqueries: The Concept That Actually Trips Students Up

This is the single biggest conceptual jump in this topic. A non-correlated subquery (both examples above) runs once, independently, and its result is reused for every row the outer query evaluates. A correlated subquery, by contrast, references a column from the outer query, which means it must re-run once for every row the outer query processes.

Worked example 3 — a correlated subquery:

Problem: Find every employee who earns more than the average salary within their own department (not the company-wide average).

SELECT e1.name, e1.salary, e1.dept_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

How this executes: For each row in the outer query (aliased e1), the inner query re-runs, this time filtered to only the department matching that specific outer row’s dept_id. So for Alice (dept 10), the inner query calculates the average salary of dept 10 only (68,500); for Chidi (dept 20), it recalculates for dept 20 only (74,500), and so on. This is why it’s called “correlated” — the inner query’s result depends on, and changes with, the outer query’s current row.

Common assignment mistake: Writing a correlated subquery but forgetting to alias both the inner and outer references to the same table, which makes it impossible for the database (or a human reader) to tell which dept_id belongs to which query level. Always give the outer and inner instances of the same table clearly different aliases (like e1 and e2 above), and double-check that the WHERE clause inside the subquery explicitly ties back to the outer alias.

Why this matters practically: Correlated subqueries are conceptually powerful but can be slow on large tables, since the inner query genuinely re-executes for every outer row (though modern query optimizers often rewrite them internally for better performance). Recognizing when a problem requires row-by-row comparison (correlated) versus a single fixed reference value (non-correlated) is exactly what assignments in this topic are testing.

Subqueries With EXISTS: A Different Way to Ask the Same Question

EXISTS checks only whether the subquery returns any rows at all — it doesn’t care about the actual values returned, which makes it useful (and often faster) for existence checks.

Worked example 4:

Problem: Find all departments that have at least one employee earning over 80,000.

SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.dept_id
    AND e.salary > 80000
);

How this executes: For each department row, the correlated subquery checks whether any matching employee row satisfies the salary condition. If at least one row is found, EXISTS returns true and that department is included — the SELECT 1 inside is a common convention signaling “we don’t care what column is returned, only whether any row exists at all.”

Common assignment mistake: Using SELECT * or a specific column name inside an EXISTS subquery and assuming it matters — it doesn’t, since EXISTS only checks for row presence, never the actual returned values. Using SELECT 1 (or SELECT *) is purely a stylistic convention, not a functional requirement, but it’s worth knowing since it commonly appears in textbook answer keys and can otherwise look confusing.

Subqueries in the FROM Clause: Treating a Query as a Temporary Table

Worked example 5:

Problem: Find the highest-paid employee in each department, along with the department’s average salary.

SELECT e.name, e.salary, dept_avg.avg_salary
FROM employees e
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS dept_avg
ON e.dept_id = dept_avg.dept_id
WHERE e.salary = (
    SELECT MAX(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id
);

How this executes: The subquery in the FROM clause runs first, independently, producing a small temporary table of department averages — this subquery must be given an alias (dept_avg here), since SQL requires every table in a FROM clause, including a derived one, to have a name. That temporary table is then joined back to the main employees table, and a separate correlated subquery in the WHERE clause filters down to only each department’s highest earner.

Common assignment mistake: Forgetting to alias a FROM-clause subquery. Unlike subqueries in WHERE, a subquery used as a table in FROM will raise a syntax error without an alias — this is one of the most common beginner errors when subqueries first move beyond the WHERE clause.

Subquery vs. JOIN: When to Use Which

This is one of the most frequently asked questions in SQL coursework, and there’s no single universally correct answer — but a few practical guidelines help:

• Use a JOIN when you need columns from both tables in your final result. Subqueries in WHERE can only be used for filtering — they can’t pull additional columns from the subquery’s table into your final output.
• Use a subquery when you only need to filter based on a condition from another table, without needing to display any of that table’s columns.
• Use EXISTS for existence checks — it’s often more efficient than an equivalent IN subquery, especially on large tables, since the database can stop searching as soon as one matching row is found.

Worked example comparing both approaches to the same question:

Problem: Find employees who work in Engineering (dept 10).

Using a JOIN:

SELECT e.name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Engineering';

Using a subquery:

SELECT name
FROM employees
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Engineering');

Both return the same result here, but the JOIN version would let you also select d.dept_name or any other departments column directly, while the subquery version cannot — it can only use the department table’s data to filter, not display it. Understanding this distinction, covered in more depth in SQL JOIN Types Explained: INNER, LEFT, RIGHT, and FULL, is often exactly what separates a subquery-appropriate question from a JOIN-appropriate one on an assignment.

A Step-by-Step Checklist for Students Stuck on a Subquery Problem

1. Identify whether the subquery belongs in WHERE (filtering), FROM (a temporary table), or SELECT (a per-row calculated value) — this determines the syntax rules you need to follow.
2. Check whether the inner query references any column from the outer query. If it does, it’s correlated and will re-run for every outer row; if not, it’s non-correlated and runs once.
3. If a scalar (single-value) comparison like = or > is used, confirm the subquery can only ever return one row — switch to IN or EXISTS if it could return multiple.
4. Always alias a subquery used in the FROM clause, and give correlated subqueries clearly distinct table aliases from the outer query.
5. If you only need to filter rows based on another table’s data (not display its columns), a subquery is often simpler than a JOIN; if you need to display columns from both tables, use a JOIN instead.

If you’re working through a complex SQL assignment involving subqueries, correlated queries, EXISTS, or deciding between subqueries and JOINs, support with SQL coursework can help you work through the query logic and structure step by step.

FAQs

Q1: What’s the main difference between a correlated and non-correlated subquery? A non-correlated subquery runs independently and produces one fixed result reused for every row the outer query checks, while a correlated subquery references a column from the outer query and must re-run separately for each row the outer query processes, since its result depends on that row’s specific values.

Q2: Why do I get an error saying my subquery returned more than one row? This happens when a subquery is used with a single-value comparison operator (=, >, <) but returns multiple rows. Switch to IN (for a list comparison) or wrap the logic in EXISTS, or add additional filtering to the subquery so it’s guaranteed to return only one row, such as using an aggregate function like MAX() or AVG().

Q3: Is a subquery always slower than a JOIN? Not necessarily — it depends on the database engine’s query optimizer, the size of the tables involved, and the specific query structure. Many modern databases internally rewrite equivalent subqueries and JOINs into similar execution plans. As a learning tool, though, it’s more useful to choose whichever version most clearly and directly answers the question, and worry about performance optimization once you’re working with genuinely large datasets.

Q4: Do I need to alias every subquery? Only subqueries used in the FROM clause (or joined directly, as in the derived-table example above) require an alias — SQL needs a name to refer to that temporary result set as if it were a real table. Subqueries in WHERE typically don’t require an alias, since they’re used directly within a comparison rather than referenced as a table elsewhere in the query.

Q5: When should I use EXISTS instead of IN? Both can often achieve the same result for existence-style checks, but EXISTS is generally preferred, especially with larger tables or when the subquery might return NULL values, since IN can behave unexpectedly when the subquery’s result list contains a NULL value, while EXISTS doesn’t have this issue.

Related Terms Worth Knowing Before You Start

If you’re still building confidence with the fundamentals — writing basic SELECT, WHERE, and GROUP BY queries, or understanding how databases and tables relate to each other in the first place — it’s worth starting with SQL Programming Approaches | Learn Database Queries & Management, which covers that foundational ground this article assumes you already have.