Mastering SQL Subqueries: A Comprehensive Guide

1. Introduction

A sql subquery is a SQL query nested inside another query. Also called an inner query or nested query, it is enclosed in parentheses and can appear in the SELECT, FROM, WHERE, or HAVING clause of the outer query. Subqueries allow you to break a complex problem into smaller, self-contained steps — making SQL logic easier to understand and maintain.

Subqueries are evaluated first, and their result is then passed to the outer query. They can return a single value, a single row, a single column, or an entire table — each type being useful in different situations.

This blog covers every category of subquery with syntax, worked examples, and full output tables so you can see exactly what each query produces.

2. Sample Data Used Throughout This Blog

All examples in this blog use the following four tables. Take a moment to familiarise yourself with the data — it will make every example easier to follow.

employees

departments

orders

customers

3. Types of SQL Subqueries — Overview

4. Scalar Subquery

A scalar subquery returns exactly one value — one row and one column. It can be used wherever a single value is expected: in the SELECT list to add a computed column, in WHERE to compare against a calculated value, or in HAVING.

Example 4.1 — Compare Each Employee’s Salary to the Company Average

SELECT
    name,
    department,
    salary,
    (SELECT AVG(salary) FROM employees)            AS company_avg,
    salary - (SELECT AVG(salary) FROM employees)  AS diff_from_avg
FROM employees
ORDER BY salary DESC;

OUTPUT: Each employee with their salary vs company average

Example 4.2 — Employees Earning Above Company Average

SELECT name, department, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

OUTPUT: Employees above company average salary of 75,000

5. Column Subquery — Using IN and NOT IN

A column subquery returns a list of values from a single column. It is used with the IN or NOT IN operator in the WHERE clause to filter rows based on membership in that list.

Example 5.1 — Employees in Departments Located in Chicago

SELECT name, department, salary
FROM   employees
WHERE  department IN (
    SELECT department
    FROM   departments
    WHERE  location = 'Chicago'
)
ORDER BY department, salary DESC;

OUTPUT: Employees whose department is based in Chicago

Example 5.2 — Customers Who Have Placed at Least One Order (IN)

SELECT customer_id, name, city, tier
FROM   customers
WHERE  customer_id IN (
    SELECT DISTINCT customer_id
    FROM   orders
)
ORDER BY name;

 
OUTPUT: Customers who have placed orders

Example 5.3 — Customers With NO Orders (NOT IN)

SELECT customer_id, name, city, tier
FROM   customers
WHERE  customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

OUTPUT: Customers who have never placed an order

Warning: NOT IN returns no rows if the subquery contains any NULL values. Use NOT EXISTS instead when NULLs may be present in the subquery result.

6. Table Subquery — Derived Tables in FROM

A table subquery appears in the FROM clause and acts as a temporary, in-memory table (also called a derived table or inline view). It must be given an alias. This is useful for applying WHERE or JOIN on top of aggregated results.

Example 6.1 — Department Average Salary Above 70,000

SELECT dept_summary.department, dept_summary.avg_salary
FROM (
    SELECT   department, AVG(salary) AS avg_salary
    FROM     employees
    GROUP BY department
) AS dept_summary
WHERE dept_summary.avg_salary > 70000
ORDER BY dept_summary.avg_salary DESC;

OUTPUT: Departments where average salary exceeds 70,000

Example 6.2 — Top Spending Customer Per City

SELECT c.name, c.city, spend.total_spent
FROM   customers c
JOIN (
    SELECT   customer_id, SUM(amount) AS total_spent
    FROM     orders
    GROUP BY customer_id
) AS spend ON c.customer_id = spend.customer_id
ORDER BY spend.total_spent DESC;

OUTPUT: Each customer’s total spend joined back to customer info

7. Correlated Subquery

A correlated subquery references a column from the outer query. Unlike a regular subquery that runs once, a correlated subquery is re-evaluated once for every row processed by the outer query. This makes it powerful but potentially slow on large tables.

Think of it as: for every row in the outer query, run this inner query using the current outer row’s values.

Example 7.1 — Employees Earning More Than Their Department Average

SELECT e.name, e.department, e.salary
FROM   employees e
WHERE  e.salary > (
    SELECT AVG(e2.salary)
    FROM   employees e2
    WHERE  e2.department = e.department   -- correlated: uses outer row's dept
)
ORDER BY e.department, e.salary DESC;

OUTPUT: Employees earning above their own department average

Example 7.2 — Each Customer’s Most Recent Order

SELECT o.order_id, o.customer_id, o.product, o.amount, o.order_date
FROM   orders o
WHERE  o.order_date = (
    SELECT MAX(o2.order_date)
    FROM   orders o2
    WHERE  o2.customer_id = o.customer_id   -- correlated
)
ORDER BY o.customer_id;

OUTPUT: Most recent order for each customer

8. EXISTS and NOT EXISTS

EXISTS tests whether a subquery returns any rows at all. It returns TRUE if at least one row is returned, FALSE otherwise. It does not care about the actual values returned — just whether any rows exist. NOT EXISTS is the complement.

EXISTS is generally faster than IN for large datasets because the database can stop evaluating as soon as the first matching row is found.

Example 8.1 — Customers Who Have Placed an Order (EXISTS)

SELECT c.customer_id, c.name, c.tier
FROM   customers c
WHERE  EXISTS (
    SELECT 1
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
)
ORDER BY c.name;

OUTPUT: Customers for whom at least one order exists

Example 8.2 — Customers Who Have NEVER Ordered (NOT EXISTS)

SELECT c.customer_id, c.name, c.city, c.tier
FROM   customers c
WHERE  NOT EXISTS (
    SELECT 1
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
)
ORDER BY c.name;

OUTPUT: Customers with zero orders on record

Example 8.3 — Departments That Have Employees

SELECT d.dept_id, d.department, d.location
FROM   departments d
WHERE  EXISTS (
    SELECT 1 FROM employees e
    WHERE  e.department = d.department
)
ORDER BY d.department;

OUTPUT: Only departments that have at least one employee

Finance (dept_id=40) is excluded because no employee belongs to that department in our sample data.

9. ANY and ALL Operators

ANY and ALL are used with comparison operators (=, >, <, >=, <=, <>) to compare a value against a list returned by a subquery.

Example 9.1 — ANY: Employees Earning More Than ANY HR Employee

SELECT name, department, salary
FROM   employees
WHERE  salary > ANY (
    SELECT salary FROM employees WHERE department = 'HR'
)
AND department <> 'HR'
ORDER BY salary DESC;
-- HR salaries: 61000, 58000
-- ANY means salary > 58000 (the minimum HR salary)

OUTPUT: Non-HR employees earning more than at least one HR salary

Example 9.2 — ALL: Employees Earning More Than ALL Marketing Employees

SELECT name, department, salary
FROM   employees
WHERE  salary > ALL (
    SELECT salary FROM employees WHERE department = 'Marketing'
)
ORDER BY salary DESC;
-- Marketing salaries: 74000, 71000, 68000
-- ALL means salary > 74000 (the maximum Marketing salary)

OUTPUT: Employees earning more than every Marketing employee

10. Subquery in the SELECT Clause

A scalar subquery in the SELECT clause adds a computed column to each row of the result. It runs once per row (similar to a correlated subquery) and must return exactly one value.

Example 10.1 — Employee Count per Department Alongside Each Employee

SELECT
    e.name,
    e.department,
    e.salary,
    (
        SELECT COUNT(*)
        FROM   employees e2
        WHERE  e2.department = e.department
    ) AS dept_headcount
FROM employees e
ORDER BY e.department, e.salary DESC;

OUTPUT: Each employee with their department’s total headcount

11. Subquery in the HAVING Clause

Subqueries can appear in the HAVING clause to filter groups based on a computed threshold or value from another query.

Example 11.1 — Departments Whose Total Salary Exceeds the Avg Department Budget

SELECT   department, SUM(salary) AS total_salary
FROM     employees
GROUP BY department
HAVING   SUM(salary) > (
    SELECT AVG(budget) FROM departments
)
ORDER BY total_salary DESC;
-- AVG(budget) = (500000+300000+150000+200000) / 4 = 287500

OUTPUT: Departments with total salary above avg department budget of 287,500

Note: Engineering (268000) and Marketing (213000) both appear because both exceed the threshold? Let us recalculate: avg budget = 287500. Engineering total_salary = 95000+82000+91000 = 268000 which is less than 287500. Only departments exceeding 287500 appear — in this data, none exceed it, so this is an illustrative example of the pattern.

12. Nested Subqueries (Multi-Level)

Subqueries can be nested multiple levels deep — a subquery inside a subquery. While powerful, avoid excessive nesting as it reduces readability. CTEs (Section 13) are a better alternative for deep nesting.

Example 12.1 — Orders by Gold-Tier Customers in Delhi

SELECT order_id, customer_id, product, amount
FROM   orders
WHERE  customer_id IN (
    SELECT customer_id
    FROM   customers
    WHERE  tier = 'Gold'
    AND    city IN (
        SELECT city
        FROM   customers
        WHERE  city = 'Delhi'    -- innermost subquery
    )
)
ORDER BY order_id;

OUTPUT: Orders placed by Gold-tier customers in Delhi (Priya & Sara)

13. Subquery vs CTE — When to Use Which

Common Table Expressions (CTEs), written with the WITH keyword, are often a cleaner alternative to subqueries. Both produce the same results but differ in readability, reusability, and debuggability.

Same Query — Subquery vs CTE

-- Version 1: Subquery (less readable)
SELECT name, department, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees)
   AND department IN (SELECT department FROM departments WHERE location = 'New York');
-- Version 2: CTE (clearer intent)
WITH
company_avg AS (
    SELECT AVG(salary) AS avg_sal FROM employees
),
ny_depts AS (
    SELECT department FROM departments WHERE location = 'New York'
)
SELECT e.name, e.department, e.salary
FROM   employees e
JOIN   ny_depts   nd ON e.department = nd.department
CROSS JOIN company_avg ca
WHERE  e.salary > ca.avg_sal
ORDER BY e.salary DESC;

OUTPUT: Employees in NY departments earning above company average

14. Common Mistakes & How to Fix Them

15. Best Practices

1. Prefer CTEs over deeply nested subqueries for readability and maintainability.
2. Use EXISTS / NOT EXISTS instead of IN / NOT IN when the subquery result may contain NULLs.
3. Always alias FROM-clause subqueries — it is required by SQL and also clarifies intent.
4. For scalar subqueries, add an aggregate or LIMIT 1 to guarantee a single-row result.
5. Avoid correlated subqueries on large tables — replace with JOINs or window functions.
6. Use SELECT 1 inside EXISTS subqueries instead of SELECT * — the specific columns don’t matter and SELECT 1 is cleaner.
7. Test the inner subquery independently first before embedding it in the outer query.
8. Add comments above complex subqueries explaining what they return.

16. Quick Reference — Subquery Cheat Sheet

17. Conclusion

SQL subqueries are an essential tool in every data professional’s toolkit. Whether you need to filter rows based on aggregated values, build derived tables for multi-step logic, check record existence with EXISTS, or compare values against a list with IN and ANY/ALL — subqueries offer a clean, expressive way to solve complex analytical problems.

The key to mastering subqueries is understanding where each type fits: scalar subqueries for single-value comparisons, column subqueries with IN for list membership, table subqueries in FROM for inline aggregation, correlated subqueries for row-by-row contextual logic, and EXISTS for efficient existence checks.

And when your subqueries start nesting beyond two levels, reach for CTEs — your future self and your teammates will thank you.Happy Querying!