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
sql
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:
sql
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.
sql
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:
sql
SELECT e1.name, e1.salary
FROM employees e1
WHERE salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
If you want to deepen your knowledge, check out our Back-End Development course to learn how SQL integrates with server-side programming.
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:
sql
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:
sql
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:
sql
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:
sql
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:
sql
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
sql
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
CTE Approach
sql
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.
sql
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
sql
SELECT name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
Using a Join Instead
sql
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:
sql
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:
sql
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:
sql
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:
sql
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);