Subqueries are advanced features in SQL for solving problems that require more than a single level of filtering, comparison, or aggregation. A subquery allows one SQL statement to use the result of another SQL statement as part of its logic.
Instead of retrieving intermediate results manually and then using those results in another query, you can combine both operations into a single SQL statement. Subqueries can appear in several places, including WHERE, HAVING, SELECT, and FROM clauses. They can also be used with operators such as IN, EXISTS, ANY, and ALL.
However, subqueries can initially seem confusing because SQL evaluates multiple query levels and because similar problems can often be solved using joins, common table expressions, or window functions. This article explains how subqueries work, the different types of subqueries and how to use them with common SQL operators.
1. What is a Subquery?
A subquery is a SQL query nested inside another SQL statement. Consider the following query:
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The outer query retrieves employees. The inner query calculates the average salary. The inner query is the subquery, while the query containing it is the outer query. Conceptually, SQL first needs to determine the value returned by:
SELECT AVG(salary) FROM employees;
Suppose the result is: 75000. The outer query effectively becomes:
SELECT name, salary FROM employees WHERE salary > 75000;
The important point is that the subquery allows the result of one query to become input to another query. A subquery can return:
- A single value
- A single column containing multiple values
- Multiple columns and rows
- A derived table that can be queried by the outer statement
The type of result determines which SQL operators can be used with the subquery.
Why Use Subqueries?
Subqueries are useful when the condition for one query depends on the result of another query. For example, suppose you want to find employees who earn more than the company-wide average salary. You could calculate the average manually:
SELECT AVG(salary) FROM employees;
Then use the result in another query:
SELECT name, salary FROM employees WHERE salary > 75000;
But this approach has an obvious problem. The average salary could change whenever employee data changes.
A subquery allows both operations to be combined:
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The database calculates the current average and uses it immediately.
Subqueries provide several benefits when working with SQL. They allow you to create dynamic filters without hard-coding values into your queries. They also let you combine multiple steps into a single SQL statement, which means the application does not need to run one query, receive the result, and then use that result in another query. This can make the code simpler and keep more of the data processing inside the database.
Subqueries are also useful for expressing requirements that depend on the result of another query. For example, if you want to find customers who have placed at least one order, you can use a subquery to first find the customer IDs that appear in the orders table. The outer query then uses those IDs to retrieve the customers:
SELECT name
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
);
Here, the inner query finds customers who have placed orders, while the outer query retrieves their names from the customers table.
2. A Sample Database
To demonstrate the different types of subqueries, assume a database containing the following tables: employees and departments. The employees table stores information about employees, while the departments table stores the departments they belong to. The department_id column connects the two tables.
We can create the departments table with the following SQL:
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100),
location VARCHAR(100)
);
This creates a table with three columns. The id column uniquely identifies each department, while name and location store the department’s name and location. We can then add some sample departments:
INSERT INTO departments (id, name, location)
VALUES
(1, 'Engineering', 'Tokyo'),
(2, 'Sales', 'Osaka'),
(3, 'Marketing', 'Tokyo'),
(4, 'Finance', 'Fukuoka');
The resulting departments table would look like this:
Next, we can create the employees table:
CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department_id INT, salary DECIMAL(10, 2), FOREIGN KEY (department_id) REFERENCES departments(id) );
The department_id column identifies the department where each employee works. The foreign key ensures that the department assigned to an employee exists in the departments table.
We can insert some sample employees:
INSERT INTO employees (id, name, department_id, salary) VALUES (1, 'Alice', 1, 85000), (2, 'Bob', 1, 70000), (3, 'Carol', 2, 60000), (4, 'David', 2, 55000), (5, 'Eve', 3, 75000), (6, 'Frank', 3, 65000), (7, 'Grace', 4, 90000);
The resulting employees table looks like this:
These two tables will be used throughout the following examples to show how different types of subqueries work.
3. Types of Subqueries
Subqueries can be grouped into different types based on the number of rows they return and whether they depend on the outer query. The three types covered here are single-row subqueries, multi-row subqueries, and correlated subqueries.
Single-Row Subqueries
A single-row subquery returns one value or one row. This makes it useful when the outer query needs to compare a column with a single result, such as an average, maximum, minimum, or total. For example, suppose we want to find employees who earn more than the average salary of all employees.
We can first calculate the average salary:
SELECT AVG(salary) AS average_salary FROM employees;
The result is:
The subquery returns one value, which is the average salary. We can use that result in another query:
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The inner query calculates the average salary, and the outer query finds employees whose salary is higher than that value. The result would look like this:
The important part is that the subquery produces a single value. The outer query can therefore use the > operator to compare each employee’s salary with that value.
Another example is finding employees who earn the highest salary:
SELECT name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
The inner query finds the highest salary, which is 90,000. The outer query then finds the employee or employees who earn that amount. The result is:
This is a single-row subquery because MAX(salary) produces one value.
Multi-Row Subqueries
A multi-row subquery returns more than one value. These subqueries are useful when the outer query needs to compare a value against a set of results. For example, suppose we want to find employees who work in departments located in Lagos.
We can first find the IDs of departments located in Tokyo:
SELECT id FROM departments WHERE location = 'Tokyo';
The result is:
The subquery returns two department IDs. Because it returns multiple values, we cannot use it directly with a simple = comparison. Instead, we can use the IN operator:
SELECT name, department_id
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE location = 'Tokyo'
);
The inner query finds departments located in Tokyo, which are departments 1 and 3. The outer query then finds employees whose department_id is either 1 or 3.
The result is:
This is a multi-row subquery because the inner query can return several department IDs.
Another example is finding employees whose salaries match any salary earned by employees in the Engineering department:
SELECT name, salary
FROM employees
WHERE salary IN (
SELECT salary
FROM employees
WHERE department_id = 1
);
The inner query returns the salaries of Engineering employees. The outer query then finds all employees whose salary is either 85,000 or 70,000. The result is:
The IN operator is useful here because the subquery can return multiple values. Multi-row subqueries can also be used with operators such as ANY and ALL. For example, the following query finds employees who earn more than at least one employee in the Engineering department:
SELECT name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department_id = 1
);
The Engineering department has salaries of 85,000 and 70,000. An employee only needs to earn more than one of these values to satisfy the condition. The result is:
For example, Eve earns 75,000, which is greater than Bob’s 70,000, so she satisfies the condition.
Correlated Subqueries
A correlated subquery is different because it depends on the current row of the outer query. Instead of running independently, the inner query uses a value from the outer query. For example, suppose we want to find employees whose salary is higher than the average salary of their own department. We can use a correlated subquery for this:
SELECT e.name, e.department_id, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id );
The outer query processes each employee. For every employee, the inner query calculates the average salary for that employee’s department. The condition then checks whether the employee’s salary is greater than that department average.
The query returns:
Alice earns more than the Engineering average of 77,500. Carol earns more than the Sales average of 57,500, and Eve earns more than the Marketing average of 70,000. Grace is not returned because her salary is equal to, rather than greater than, the Finance department average. The important part of the query is this condition: WHERE e2.department_id = e.department_id.
Here, e.department_id comes from the outer query, while e2.department_id belongs to the inner query. This connection between the two queries is what makes the subquery correlated. Correlated subqueries are useful when the comparison needs to be made against a value that is specific to each row, such as comparing an employee with their department average rather than the average salary of the entire company.
Another use of correlated subqueries is checking whether a related row exists. For example, we can find departments that have at least one employee:
SELECT d.name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);
The inner query checks whether an employee exists for the current department. The d.id value comes from the outer query, so the subquery is correlated. The result is:
Every department in our sample data has at least one employee, so all four departments are returned. If we wanted to find departments that have no employees, we could use NOT EXISTS:
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);
In our current sample data, this query would return no rows because every department has an employee. The important difference is that EXISTS checks whether at least one matching row exists, while NOT EXISTS checks that no matching row exists.
A single-row subquery returns one value and is commonly used with comparison operators such as
=, >, or <. A multi-row subquery returns several values and is commonly used with IN, ANY, or ALL. A correlated subquery depends on the current row of the outer query and is often used with EXISTS or when comparing a row with a value calculated for its group.4. Some Common Examples of Using SQL Subqueries
Let us look at some common ways they are used in real SQL queries. Subqueries can appear in different parts of a SQL statement, such as the WHERE, SELECT, and FROM clauses. They can also be used with commands such as UPDATE and DELETE.
Subqueries in the WHERE Clause
One of the most common places to use a subquery is inside a WHERE clause. The subquery calculates a value or produces a list of values, and the outer query uses those results for filtering.
For example, suppose we want to find employees who earn more than the average salary of all employees.
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The inner query calculates the average salary for all employees. The outer query then returns employees whose salary is greater than that average.
Using IN with Subqueries
The IN operator is useful when a subquery returns multiple values. It allows the outer query to check whether a value exists in a list returned by the subquery. Suppose we want to find employees who work in departments located in Tokyo or Kyoto.
SELECT name, department_id
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE location IN ('Tokyo', 'Kyoto')
);
The inner query finds department IDs for departments located in Tokyo and Kyoto. The outer query then finds employees whose department_id is either 1 or 3. The IN operator works well whenever a subquery returns multiple values.
NOT IN with Subqueries
NOT IN performs the opposite operation. It returns rows whose values do not appear in the subquery result. Suppose we want to find employees who do not work in departments located in Tokyo.
SELECT name, department_id
FROM employees
WHERE department_id NOT IN (
SELECT id
FROM departments
WHERE location = 'Tokyo'
);
It is important to remember that NOT IN can behave unexpectedly when the subquery contains NULL values. In such situations, NOT EXISTS is often a safer option.
Using EXISTS with Subqueries
EXISTS checks whether the subquery returns at least one row. It is commonly used with correlated subqueries. Suppose we want to find departments that have employees.
SELECT d.name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);
The inner query checks whether employees exist for the current department. All departments will be returned because every department has employees.
We can also use NOT EXISTS to find departments without employees:
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);
This query would return no rows because every department currently has employees.
Subqueries in the SELECT Clause
Subqueries can also appear in the SELECT list. This allows additional information to be displayed for each row. Suppose we want to display each employee along with the average salary of all employees.
SELECT
name,
salary,
(
SELECT AVG(salary)
FROM employees
) AS company_average
FROM employees;
The subquery calculates the average salary once, and the value is displayed for every employee. This makes it easy to compare each employee’s salary with the company average.
Subqueries in the FROM Clause
A subquery in the FROM clause creates a temporary result set that can be treated like a table. This is sometimes called a derived table. Suppose we want to calculate the average salary for each department.
SELECT *
FROM (
SELECT
department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS department_averages;
The inner query produces this temporary result:
The outer query treats this result like a normal table. We can also filter the temporary result:
SELECT *
FROM (
SELECT
department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS department_averages
WHERE average_salary > 70000;
The result is:
This technique is useful when one query needs to process the results of another query.
Subqueries with UPDATE
Subqueries are not limited to SELECT statements. They can also be used with UPDATE. Suppose employees in departments located in Tokyo should receive a salary increase.
UPDATE employees
SET salary = salary * 1.10
WHERE department_id IN (
SELECT id
FROM departments
WHERE location = 'Tokyo'
);
The inner query finds the department ID for Tokyo. The outer UPDATE then increases the salary of employees belonging to that department by 10%. Subqueries can be used inside UPDATE statements to identify the rows that should be modified.
Before running an UPDATE, it is a good practice to use the same condition with a SELECT statement to check which rows will be affected:
SELECT *
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE location = 'Tokyo'
);
This lets you verify the affected employees before changing the data. For production databases, extra care should be taken with UPDATE statements because a condition that matches more rows than expected can modify many records at once.
Subqueries with DELETE
Subqueries can also be used to determine which rows should be removed. Suppose we want to delete departments that do not have employees.
DELETE FROM departments
WHERE NOT EXISTS (
SELECT 1
FROM employees
WHERE employees.department_id = departments.id
);
The subquery checks whether employees exist for each department. Only departments without employees would be deleted.
As with UPDATE, it is usually safer to test the condition first:
SELECT *
FROM departments
WHERE NOT EXISTS (
SELECT 1
FROM employees
WHERE employees.department_id = departments.id
);
Always verify the result before deleting data.
Subqueries and CASE Expressions
Subqueries can also be combined with CASE expressions to classify or label data. Suppose we want to show whether each employee earns above or below the company average.
SELECT
name,
salary,
CASE
WHEN salary > (
SELECT AVG(salary)
FROM employees
)
THEN 'Above Average'
ELSE 'Average or Below'
END AS salary_status
FROM employees;
The subquery calculates the company average salary, and the CASE expression uses that value to classify employees. The result is:
5. Conclusion
SQL subqueries provide a flexible way to use the result of one query inside another. They can filter data, compare values, check whether related records exist, create temporary result sets, and even control UPDATE and DELETE operations.
The key is to choose the right approach for the problem. IN works well with lists of values, EXISTS is useful for checking related rows, and correlated subqueries are useful when the inner query depends on the current row of the outer query. With practice, subqueries become a useful tool for writing clear and expressive SQL.
