SQL Subqueries: A Complete Guide with Examples

A subquery is a query nested inside another query. They’re powerful for multi-step data retrieval.

Subquery in WHERE

SELECT name, price
FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = true
);

The inner query runs first, returning active category IDs. The outer query uses them.

Subquery with Comparison

-- Products more expensive than the average
SELECT name, price
FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

-- The employee with the highest salary
SELECT name, salary
FROM employees
WHERE salary = (
  SELECT MAX(salary) FROM employees
);

Subquery in SELECT

SELECT
  name,
  price,
  (SELECT AVG(price) FROM products) AS avg_price,
  price - (SELECT AVG(price) FROM products) AS price_difference
FROM products;

Useful for adding computed columns that reference other tables or aggregates.

Subquery in FROM

SELECT department, avg_salary
FROM (
  SELECT
    department_id,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 50000;

The subquery creates a temporary table that the outer query treats like a regular table.

Correlated Subqueries

A correlated subquery references columns from the outer query. It runs once for each row in the outer query.

-- Employees who earn more than the average in their department
SELECT name, salary, department_id
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

For each employee, the inner query calculates the average salary of that employee’s department.

EXISTS and NOT EXISTS

-- Customers who have placed at least one order
SELECT name, email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Products that have never been ordered
SELECT name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

EXISTS is often faster than IN for large result sets because it stops as soon as it finds a match.

Subquery with ANY and ALL

-- Products more expensive than ANY product in category 5
SELECT name, price
FROM products
WHERE price > ANY (
  SELECT price FROM products WHERE category_id = 5
);

-- Products more expensive than ALL products in category 5
SELECT name, price
FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE category_id = 5
);

Row Subqueries

-- Find users whose city and state match an office
SELECT name
FROM users
WHERE (city, state) = (
  SELECT city, state FROM offices WHERE is_headquarters = true
);

Nested Subqueries

SELECT name
FROM employees
WHERE department_id IN (
  SELECT id FROM departments
  WHERE location_id IN (
    SELECT id FROM locations WHERE country = 'US'
  )
);

Three levels deep. Each subquery returns results used by its parent.

Subquery vs JOIN

Often the same result can be written as a JOIN or a subquery:

-- Subquery version
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);

-- JOIN version (same result)
SELECT DISTINCT c.name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

When to use which:

Subquery	JOIN
Simple, readable (few columns needed)	Better performance with large datasets
EXISTS/NOT EXISTS patterns	Need columns from both tables
Aggregation in WHERE	Complex multi-table queries
One-time computation	Frequently reused

Performance Tips

Use EXISTS instead of IN when checking for existence in large sets
Use JOIN instead of subquery when you need columns from the subquery table
Add indexes on columns used in subquery WHERE clauses
Limit nested levels — more than 3 levels deep is hard to read and maintain
Use CTEs (WITH clause) for complex queries — they’re more readable and sometimes faster

CTE Alternative

WITH dept_stats AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_stats d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

CTEs make complex subqueries easier to read and debug.

Related: Master SQL JOINs and check our SQL cheat sheet.