Whenever you see a query nested inside another query in SQL, that's a subquery. Also known as an inner query, a subquery sits within a main or outer query. Subqueries provide the main query with additional data—either as a derived column, a derived table, or as a filter for the rows returned.
Subqueries can be tricky, especially for beginners diving into SQL. This article simplifies the concept, breaking it down into clear steps. By the end, you'll be able to use subqueries with confidence to solve real-world data problems.
Table of Contents
- Prerequisites
- How Subqueries Work
- Execution Order
- Types of Subqueries
- Non-correlated Subqueries
- Correlated Subqueries
- Conclusion
Prerequisites
Subqueries are an advanced SQL topic, so you should be comfortable with the fundamentals: SELECT, FROM, WHERE, joins, the CASE statement, and the logical order of query execution. If you're solid on those, you're ready to move on.
How Subqueries Work
Let's start with a practical example. Consider the following query that uses a subquery:
SELECT *
FROM registration
WHERE student_id
IN (SELECT id
FROM student
WHERE location = 'Lagos');
This query has two parts: the main query and the subquery. The main query is the outer part:
SELECT *
FROM registration
WHERE student_id
IN (...);
Notice that the brackets are empty in the main query above; they're filled by the subquery. The subquery, enclosed in parentheses, is used to filter the rows returned by the main query. Here's the subquery portion:
SELECT id
FROM student
WHERE location = 'Lagos';
The subquery executes first, returning a list of student IDs from Lagos. The main query then uses that list to retrieve all registration records for those students. This is the essence of a subquery: it provides data to the outer query to narrow down results.
Execution Order
In most database systems, non-correlated subqueries execute once, before the main query runs. The result is then passed to the main query. Correlated subqueries, on the other hand, execute multiple times—once for each row processed by the main query. Understanding this distinction is key to writing efficient queries.
Types of Subqueries
There are two primary types of subqueries in SQL:
Non-correlated Subqueries
A non-correlated subquery is independent of the main query. It can run on its own and returns a result that the main query uses. The example above is non-correlated because the subquery doesn't reference any columns from the outer query. These are straightforward and typically perform well.
Example:
SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Engineering');
The subquery runs once, fetches the Engineering department's ID, and then the main query selects employees from that department.
Correlated Subqueries
A correlated subquery references columns from the main query, making it dependent on the outer query. It executes once for each row in the main query's result set, which can be slower but is powerful for row-by-row comparisons.
Example:
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
Here, the subquery computes the average salary for the department of each employee in the outer query. It runs repeatedly, once per employee, to compare individual salaries against departmental averages.
Conclusion
Subqueries are a flexible tool in SQL, allowing you to break complex problems into manageable parts. Non-correlated subqueries are efficient for static data retrieval, while correlated subqueries handle dynamic, row-by-row logic. As you practice, you'll find them indispensable for writing clean, expressive queries. In 2026, with modern SQL databases and query optimizers, subqueries remain a core skill for any data professional. Keep experimenting—subqueries will soon become second nature.
via FreeCodeCamp