SQL Subquery: Syntax, Usage, and Examples

A SQL subquery is a query nested inside another SQL query. It helps break down complex queries into smaller, more manageable parts while improving readability. You can use a subquery to filter results dynamically, compute derived values, or create virtual tables within your main query.

How to Use SQL Subqueries

Subqueries can appear in different parts of a SQL statement. They are commonly found in the SELECT, FROM, WHERE, and HAVING clauses, depending on the use case.

Basic SQL Subquery Syntax

SELECT column1, column2
FROM table1
WHERE column3 = (SELECT column3 FROM table2 WHERE condition);

The subquery executes first and passes its result to the main query. This allows for flexible data retrieval without needing multiple separate queries.

When to Use SQL Subqueries

Subqueries are useful when filtering results dynamically based on another query. For example, they can help find employees with salaries above the company average:

SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

When working with multiple tables, a subquery can pre-filter data before joining tables. This approach makes queries more structured and improves efficiency.

SELECT e.name, d.department_name
FROM employees e
JOIN (SELECT department_id, department_name FROM departments WHERE location = 'New York') d
ON e.department_id = d.department_id;

Another common use case is correlated subqueries, which reference columns from the outer query. These are useful for tasks like finding employees earning above the average salary of their department:

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

Examples of SQL Subqueries

Subqueries can act as filters in WHERE clauses. This is helpful when searching for customers who have placed at least one order:

SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

They can also compute values dynamically inside the SELECT clause. This allows for real-time calculations like displaying employee salaries alongside the company’s average salary:

SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

When subqueries appear inside the FROM clause, they act as virtual tables that simplify complex queries. Suppose you need to calculate average salaries per department and then join them with department names:

SELECT dept_name, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) d
JOIN departments ON d.department_id = departments.department_id;

Correlated subqueries provide even more dynamic filtering by executing for each row of the main query. A useful example is checking if an employee has the highest salary in their department:

SELECT name, salary
FROM employees e1
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

Subqueries work well with aggregate functions to find customers whose order totals exceed the average order value:

SELECT customer_id
FROM orders
WHERE total_price > (SELECT AVG(total_price) FROM orders);

Learn More About SQL Subqueries

SQL Subquery vs. Common Table Expressions (CTEs)

A Common Table Expression (CTE) is similar to a subquery but defined using WITH. Unlike subqueries, CTEs improve readability and allow for multiple references within a query.

Subquery Approach

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

CTE Approach

WITH SalaryData AS (SELECT AVG(salary) AS avg_salary FROM employees)
SELECT employee_id, salary
FROM employees, SalaryData
WHERE salary > avg_salary;

Using a CTE is often preferable when reusing the same subquery multiple times in a query.

Using Subqueries in the FROM Clause

A subquery inside the FROM clause creates a virtual table that the main query can use. This technique is helpful when you need to group or aggregate data before performing further operations.

SELECT avg_salary
FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS salary_table;

SQL Subquery vs. Joins

Subqueries return a single value or result set and work well when dealing with aggregations. However, JOIN statements are more efficient when combining multiple tables.

Using a Subquery for Filtering

SELECT name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Using a Join Instead

SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location = 'New York';

Joins generally perform better than subqueries since they allow databases to optimize query execution plans.

Using Subqueries with Joins

Subqueries can also appear within joins to filter data before merging tables. Suppose you want to retrieve employees from departments located in Los Angeles:

SELECT e.name, d.department_name
FROM employees e
JOIN (SELECT department_id, department_name FROM departments WHERE location = 'Los Angeles') d
ON e.department_id = d.department_id;

Additional SQL Subquery Examples

Finding employees without a manager requires a subquery that checks for missing IDs in the employee list:

SELECT name
FROM employees
WHERE manager_id NOT IN (SELECT employee_id FROM employees);

Retrieving customers who have ordered a specific product involves filtering based on product_id:

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE product_id = 10);

Finding each customer’s most recent order uses a correlated subquery to match their latest order date:

SELECT customer_id, order_date
FROM orders o1
WHERE order_date = (SELECT MAX(order_date) FROM orders o2 WHERE o1.customer_id = o2.customer_id);