Subqueries

#Introduction

This chapter opens the advanced SQL phase with one of its most important topics: subqueries, also called nested queries — heavily used in banking, payroll, e-commerce, HRMS, reporting systems, analytics dashboards, Spring Boot backend APIs and SQL interviews.

By the end you'll confidently use single-row and multi-row subqueries; subqueries in WHERE, SELECT and FROM; nested subqueries; IN, NOT IN, EXISTS, NOT EXISTS, ANY and ALL; scalar subqueries and derived tables — and you'll know the NOT IN + NULL problem and the performance considerations.

#What Is a Subquery?

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

SELECT AVG(salary) FROM employees is the inner query (subquery); SELECT * FROM employees WHERE salary > (...) is the outer query.

If the average salary is 69,000, the query conceptually becomes SELECT * FROM employees WHERE salary > 69000;.

#Sample Employee Table

#Types of Subqueries

#Single-Row Subqueries

A single-row subquery returns exactly one row, and is used with =, >, <, >=, <= or <>.

Highest-Paid Employee

SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

The inner query returns 90000, so the outer query becomes WHERE salary = 90000 → David, 90000.

Why Not Just ORDER BY + LIMIT?

SELECT * FROM employees ORDER BY salary DESC LIMIT 1; also works — but if two employees earn 90000, the subquery version returns all employees tied for the highest salary, while LIMIT 1 returns only one row.

Lowest-Paid and Above-Average Employees

SELECT *
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);   -- above 69,000

#Multi-Row Subqueries

A multi-row subquery returns several rows, so you generally can't use = with it. Use IN, ANY or ALL instead.

If a subquery returns IT and HR, WHERE department = (SELECT ...) fails with Subquery returns more than 1 row; write WHERE department IN (SELECT ...) instead.

#IN with Subqueries

Find customers who placed at least one order:

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

The subquery returns 101, 102, 101, 103; the outer query checks whether each customer_id is present in that result → John, David, Lisa.

DISTINCT in an IN Subquery

You may write IN (SELECT DISTINCT customer_id FROM orders), but don't add DISTINCT automatically — for membership, duplicates don't change the logical result, and the optimizer may already handle it efficiently. Check the execution plan.

#NOT IN

Find customers who never placed an order:

SELECT *
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

Expected result: Mary. However, NOT IN has an extremely important problem.

#The NOT IN + NULL Problem

Suppose the subquery returns 101, 102, 103 and NULL:

WHERE customer_id NOT IN (101, 102, 103, NULL)

SQL uses three-valued logic — TRUE, FALSE and UNKNOWN. customer_id <> NULL is always UNKNOWN, so NOT IN + NULL makes the condition never true and the query returns no rows — even though Mary has no orders.

Safer Solution — NOT EXISTS

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

You could filter NULLs explicitly (... WHERE customer_id IS NOT NULL), but when the requirement is rows with no matching related row, NOT EXISTS is clearer.

#EXISTS

EXISTS checks whether the subquery returns at least one row:

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

For each customer it asks: does at least one matching order exist? Yes → return the customer; no → skip it.

Why SELECT 1?

A common interview question. EXISTS cares only about whether a row exists, not what the row contains — so SELECT 1 clearly says "I only care about existence." SELECT * inside EXISTS behaves the same; SELECT 1 is just a clear, widely used convention.

#NOT EXISTS

NOT EXISTS is true when no matching row exists:

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);   -- Mary

#IN vs EXISTS

-- IN: is this value present in the result set?
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- EXISTS: does at least one matching row exist?
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Performance

A common oversimplification is "EXISTS is always faster than IN." Not universally true. Modern MySQL optimizers transform many IN, EXISTS and JOIN queries (semi-join strategies). Performance depends on table sizes, indexes, data distribution, query structure, MySQL version and the execution plan.

#NOT IN vs NOT EXISTS

For anti-matching requirements — customers without orders, products never sold, employees without assignments — NOT EXISTS is often the safer and clearer choice.

#ANY

ANY means the comparison must be true for at least one value. HR salaries are 50000 and 55000:

SELECT *
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department = 'HR'
);

Salary greater than at least one HR salary. Since the smallest HR salary is 50000, any salary above it qualifies — for this comparison, > ANY(...) is conceptually like comparing with MIN(...) (for a non-empty subquery, with NULLs handled).

#ALL

ALL means the comparison must be true for every value:

SELECT *
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'HR'
);

The employee must earn more than every HR employee — more than 55000. For this comparison, > ALL(...) is conceptually like comparing with MAX(...).

ANY vs ALL

#Subqueries in WHERE

The most common location — comparing against aggregates, membership checks, existence checks and filtering based on another table:

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);

#Subqueries in SELECT — Scalar Subqueries

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

The subquery returns one value, displayed beside every row. A subquery used where a single value is expected is a scalar subquery — it must return at most one row; otherwise MySQL raises Subquery returns more than 1 row.

SELECT order_id,
       amount,
       (SELECT AVG(amount) FROM orders) AS average_order_amount
FROM orders;

Useful for reports that compare each row with a company-wide metric.

#Subqueries in FROM — Derived Tables

A subquery in the FROM clause creates a derived table:

SELECT AVG(department_total_salary)
FROM (
    SELECT department,
           SUM(salary) AS department_total_salary
    FROM employees
    GROUP BY department
) AS department_salary;

The inner query creates a temporary logical result:

The outer query then calculates the average department payroll.

SELECT *
FROM (
    SELECT department,
           AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
) AS department_average
WHERE average_salary > 60000;

First calculate the average salary per department, then keep departments averaging above 60,000.

#Nested Subqueries

A subquery can contain another subquery:

SELECT *
FROM employees
WHERE department = (
    SELECT department
    FROM employees
    WHERE salary = (
        SELECT MAX(salary)
        FROM employees
    )
);

An Important Problem with This Query

If several top earners belong to different departments, the middle subquery returns multiple rows — but the outer query uses =, which expects a single value. Safer:

SELECT *
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    WHERE salary = (SELECT MAX(salary) FROM employees)
);

Choose operators based on how many rows the subquery can return.

#Real-World Business Examples

1. Highest-Revenue Customer

SELECT *
FROM customers
WHERE customer_id = (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    ORDER BY SUM(amount) DESC
    LIMIT 1
);

This returns one customer — if several are tied, LIMIT 1 returns only one. A tie-safe version with nested aggregation:

SELECT c.*
FROM customers c
WHERE c.customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) = (
        SELECT MAX(total_amount)
        FROM (
            SELECT customer_id, SUM(amount) AS total_amount
            FROM orders
            GROUP BY customer_id
        ) AS customer_totals
    )
);

(MySQL doesn't allow LIMIT inside an IN/ANY/ALL subquery, which is one more reason for this form.) Window functions make ranking problems much cleaner later.

2. Products Never Sold

SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.product_id = p.product_id
);

3. Employees Above the Company Average

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

Company average ≠ department average. Comparing each employee with their own department's average needs a correlated subquery (next chapter) or another advanced technique.

4. Customers with Orders

SELECT *
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

5. Highest Monthly Revenue

SELECT *
FROM monthly_sales
WHERE revenue = (SELECT MAX(revenue) FROM monthly_sales);

Returns all months tied for the highest revenue.

6. Orders Above the Average Order Amount

SELECT *
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

7. Departments Above the Company-Average Salary

SELECT department,
       AVG(salary) AS department_average
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Subqueries can also be used inside HAVING conditions.

#Second-Highest Salary

One of the most frequently asked SQL interview questions.

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

Employee(s) with the Second-Highest Salary

SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (SELECT MAX(salary) FROM employees)
);

Returns all employees tied at the second-highest distinct salary. With salaries 90000, 90000, 80000, 70000, the second-highest distinct salary is 80000, not 90000 — the MAX() approach handles distinct levels naturally.

#Department with the Highest Payroll

SELECT department,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) = (
    SELECT MAX(department_total)
    FROM (
        SELECT SUM(salary) AS department_total
        FROM employees
        GROUP BY department
    ) AS department_totals
);

Returns every department tied for the highest payroll.

#Performance Considerations

Not every problem should automatically be solved with a subquery. Alternatives include JOINs, CTEs, window functions, pre-aggregated tables and proper indexes.

-- Subquery
SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- JOIN (needs DISTINCT, because a customer can have many orders)
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

-- EXISTS
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

All three express similar requirements. The best choice depends on correctness, readability, duplicates, NULL semantics, indexes, the execution plan and actual performance.

#Subquery vs JOIN

A subquery is often natural when comparing against an aggregate, checking membership or existence, or using one query's result to filter another:

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

A JOIN is often natural when combining columns from multiple related tables:

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

#Mini Project

Using the employees table (John IT 70000, David IT 90000, Lisa HR 50000, Alex HR 55000, Mary Finance 80000) and orders (101 → 5000, 102 → 8000, 101 → 2500, 103 → 7000):

-- 1. Employees earning above the company average
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 2. The highest-paid employee
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 3. Customers with orders
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 4. Customers without orders
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 5. The department with the highest total salary
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) = (
    SELECT MAX(department_total)
    FROM (SELECT SUM(salary) AS department_total
          FROM employees
          GROUP BY department) AS department_totals
);

-- 6. The company average beside every employee
SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS company_average
FROM employees;

#Practice Problems

Easy

1. Find the employee with the highest salary.
2. Find the employee with the lowest salary.
3. Find employees above the company average salary.
4. Find customers who placed orders.
5. Find customers who never placed orders.
6. Find orders above the average order amount.
7. Display the company average salary beside every employee.

Medium

1. Find departments whose average salary exceeds 70,000.
2. Find products never sold.
3. Find customers whose purchase amount is above the average order amount.
4. Find the department with the highest payroll.
5. Find the second-highest distinct salary.
6. Find all employees earning the second-highest salary.
7. Find employees whose salary is greater than at least one HR employee.
8. Find employees whose salary is greater than every HR employee.

Interview level

1. Compare IN vs EXISTS.
2. Compare NOT IN vs NOT EXISTS.
3. Explain the NOT IN + NULL problem.
4. Explain scalar subqueries.
5. Explain derived tables.
6. Find the department with the highest average salary.
7. Find customers who placed at least one order without returning duplicates.
8. Rewrite an IN query using EXISTS.
9. Rewrite a subquery using a JOIN.
10. Explain when a JOIN is preferable to a subquery.
11. Explain why EXISTS isn't always faster than IN.
12. Explain ANY vs ALL.
13. Find all employees in the department of the highest-paid employee.
14. Handle multiple highest-paid employees in different departments.
15. Analyze a subquery using EXPLAIN.

#Real Backend Example

GET /api/customers/inactive — customers who have never placed an order:

SELECT c.customer_id,
       c.customer_name,
       c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Supporting index:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Extremely common for customers without orders, products never sold, employees without assignments, users without logins, accounts without transactions and events without bookings.