SQL Subqueries: A Complete Guide with Examples

Databases Databases 8 min read 1500 words Beginner ExcellentWiki Editorial Team

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

Subqueries are particularly valuable when you need to break a complex problem into manageable steps — instead of fetching data and running a second query from your application code, you let the database do the orchestration. This reduces network round trips and leverages the database’s query optimizer, which often finds efficient execution paths that application-level code would miss. However, subqueries also come with trade-offs: they can be harder to debug, and poorly written subqueries can lead to performance problems if the database planner misestimates row counts.

Understanding the different types of subqueries and where each fits is essential for writing efficient SQL. A subquery can return a single value (scalar), a single column (columnar), or a full result set (table). Where you place the subquery — in WHERE, SELECT, FROM, or as part of EXISTS — determines how the database processes it and what kinds of problems it solves.

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.

Scalar subqueries in the SELECT clause must return exactly one row and one column. If they return multiple rows, the database throws an error. This makes them ideal for row-level comparisons, such as showing how each product compares to an overall average. You can also use them to pull related counts or totals into every row without a separate JOIN.

For comparison operators like > and =, the subquery must also be scalar. This pattern is common for threshold-based filtering: products above average price, employees earning the maximum salary, or accounts with balances exceeding a department mean. Because a non-correlated subquery runs only once, its cost is typically negligible and the database can cache the result for the duration of the outer query.

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.

Correlated subqueries are the most powerful but also the most performance-sensitive type. Because they execute once per outer row, a correlated subquery on a 100,000-row table runs 100,000 times. If the inner query does a sequential scan, that is catastrophic. Always ensure the columns used in the correlation — the department_id = e.department_id condition — are indexed. A correlated subquery with proper indexing often outperforms alternatives like fetching all data and filtering in application code.

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.

The EXISTS keyword is closely related to correlated subqueries. It checks whether any rows match the inner condition rather than returning actual data. Database optimizers treat EXISTS specially: they perform a semi-join that stops scanning as soon as the first match is found. This makes EXISTS significantly faster than IN when the subquery could return many rows. Use EXISTS for “has at least one” or “has none” patterns — they are the most readable and often the most efficient choice.

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.

Nested subqueries, while powerful, come with readability costs. Each level of nesting adds mental overhead for the next developer reading the code. A good rule of thumb is to limit nesting to three levels. Beyond that, refactor into CTEs or break the query into separate steps.

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;

The choice between subqueries and JOINs is not always clear-cut. Modern query optimizers often produce identical execution plans for logically equivalent forms, so the difference is frequently stylistic. However, practical considerations exist. JOINs give you direct access to columns from both tables in the result set, which is cleaner than repeating subqueries. Subqueries in WHERE (especially with EXISTS) are often clearer for existence checks. For aggregation-based filtering — “show me departments where average salary exceeds X” — a subquery or CTE is usually more natural than a self-JOIN with GROUP BY. Performance testing on real data volumes is the only way to know for sure. Write the query both ways, run EXPLAIN ANALYZE on both, and compare.

| Subquery | JOIN | |

FAQ

What is the difference between SQL and NoSQL? SQL databases use structured schemas with tables, relationships, and ACID transactions. NoSQL databases offer flexible schemas, horizontal scaling, and various data models (document, key-value, graph, column-family). Choose based on data structure and access patterns.

When should I use an index? Index queries filtering on the indexed column(s), particularly for WHERE clauses, JOINs, and ORDER BY. Avoid over-indexing — each index slows writes and consumes storage. Monitor slow queries to identify indexing opportunities.

What is database normalization? Normalization organizes data to reduce redundancy and improve integrity. First Normal Form (1NF) eliminates duplicate columns. Second Normal Form (2NF) removes partial dependencies. Third Normal Form (3NF) removes transitive dependencies.

How do I choose between PostgreSQL and MySQL? PostgreSQL offers more advanced features (JSONB, full-text search, custom types, better concurrency). MySQL is simpler to configure and widely available. Both are excellent choices; PostgreSQL is often preferred for complex applications.

What is a deadlock in databases? A deadlock occurs when two transactions each hold locks the other needs. The database detects deadlocks and kills one transaction automatically. Reduce deadlocks by accessing tables in the same order across transactions and keeping transactions short.

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 — the index benefits the inner query the same way it would standalone
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

For subqueries in FROM (derived tables), remember that PostgreSQL requires an alias for every derived table. Unlike some databases that materialize derived tables, PostgreSQL typically optimizes them together with the outer query, but verify with EXPLAIN ANALYZE if performance is critical.

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;