Pathubs SQL Curriculum • Module 11

SQL Subqueries Mastery

Master nested queries and modular SQL problem-solving: understand scalar subqueries, multi-row lookups with IN, boolean existence testing with EXISTS, derived tables in FROM, row-by-row correlated subqueries, and Subquery vs JOIN tradeoffs.

⏱️ Estimated Time:35 Minutes

🎯 Level:Intermediate SQL Logic

📊 Track:Data Analytics & Backend Engineering

✨ Mode:Interactive Subquery Lab

Introduction: Solving Two-Step Analytical Problems

Suppose your manager asks: “Which employees earn more than the company average salary?”

Notice that this question fundamentally requires two distinct steps:

Step 1: Calculate the company-wide average salary (e.g. $73,333).
Step 2: Filter all employees whose individual salary is strictly greater than that calculated average (salary > 73333).

Instead of manually executing two separate queries, copying the number, and pasting it into the second query, SQL allows you to nest Step 1 directly inside Step 2 using a Subquery.

What Is a Subquery? (The Core Mental Model)

A Subquery (also called an inner query or nested query) is a SELECT statement placed inside parentheses within another SQL statement (the outer query).

-- Outer query selects matching employee names
SELECT name, salary
FROM employees
WHERE salary > (
-- Inner subquery calculates the dynamic benchmark
SELECT AVG(salary) FROM employees
);

Diagram 1: The Subquery Data Flow Mental Model

Outer Query
Needs Dynamic Benchmark

── Evaluates Inner ──▶

Inner Subquery
AVG(salary) = $73,333

── Passes Result ──▶

Outer Execution
WHERE salary > 73333

How a Subquery Works: Concrete Step-by-Step Walkthrough

Consider three sample employee records:

id	name	salary	Evaluation
1	Rahul	$60,000	$60,000 > $60,000 ➔ FALSE
2	Amit	$40,000	$40,000 > $60,000 ➔ FALSE
3	Priya	$80,000	$80,000 > $60,000 ➔ TRUE (Returned)

The inner query evaluates (60000 + 40000 + 80000) / 3 = 60000. The outer query then checks every row against 60000. Priya is the only employee who qualifies.

Scalar Subqueries: Single-Value Outputs

Definition

A Scalar Subquery is an inner query that returns exactly 1 row and 1 column (a single discrete value).

Because it returns a single scalar value, you can compare it with standard mathematical operators (=, >, <, >=, <=, !=).

Subqueries in WHERE Clauses: Dynamic Filtering

Practical business examples using scalar subqueries in WHERE:

-- 1. Find the top-earning employee(s)
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 2. Find high-value orders above the average order amount
SELECT id, customer_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

Multi-Row Subqueries With IN

When an inner query returns a list of multiple values (one column with multiple rows), use the IN operator to test membership:

-- Find employees working in departments located in 'Mumbai'
SELECT name, department_id
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE location = 'Mumbai'
);

Diagram 2: Subquery With IN Multi-Value Membership

Inner Query
SELECT id WHERE location = 'Mumbai'

── Yields List ──▶

ID List
[1, 3]

── Evaluates ──▶

Outer Filter
WHERE department_id IN (1, 3)

IN vs. = With Subqueries: The Fundamental Rule

Crucial Rule: Scalar (=) vs List (IN)

= expects a single value (fails if inner returns 2+ rows).
IN expects a list of 0 to N values (safely matches across rows).

-- ❌ FAILS: Error "more than one row returned by a subquery" if 2+ departments match!
WHERE department_id = (SELECT id FROM departments WHERE location = 'Mumbai');

-- ✅ SAFE: Handles 0, 1, or 1,000 returned department IDs seamlessly
WHERE department_id IN (SELECT id FROM departments WHERE location = 'Mumbai');

Row Existence Testing With EXISTS

EXISTS tests whether a subquery returns at least one matching row. It evaluates to TRUE immediately upon finding a match without needing to load full tables:

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

Diagram 3: EXISTS Boolean Evaluation

Candidate Customer
Rahul (ID: 101)

── Match in Orders? ──▶

Found Order #501
EXISTS ➔ TRUE (Kept)

EXISTS vs. IN: Practical Differences

Feature	IN Subquery	EXISTS Subquery
Evaluation Model	Extracts a list of values, then tests membership	Correlates row-by-row and checks if ≥ 1 match exists
NULL Safety	`NOT IN` can fail silently if inner query has `NULL`s	`NOT EXISTS` handles `NULL` rows safely and predictably
Best Used For	Simple discrete lookup sets (e.g. status codes, country IDs)	Complex table relationships and existence validations

Subqueries in FROM Clauses (Derived Tables)

A subquery placed in the FROM clause acts as a temporary inline table (derived table). Standard ANSI SQL requires assigning a table alias:

SELECT department, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_summaries
WHERE avg_salary > 70000;

Subqueries in SELECT Column Lists

You can project a scalar subquery as a computed column in your SELECT list alongside normal table columns:

SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Correlated Subqueries (Row-by-Row Evaluation)

In a Correlated Subquery, the inner query references a column from the outer query (e.g. e2.department_id = e1.department_id). The inner query dynamically recalculates for every candidate outer row:

-- Find employees earning more than THEIR OWN department's average
SELECT e1.name, e1.department, e1.salary
FROM employees AS e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e1.department_id
);

Diagram 4: Correlated Subquery Execution Loop

Outer Candidate Row
Priya (Eng, $110k)

── Passes dept_id=1 ──▶

Inner Query
Avg for Dept 1 = $91,667

── Evaluates ──▶

Comparison
$110k > $91.6k ➔ QUALIFIED

Subquery vs. JOIN: When to Use Which

Scenario	Preferred Approach	Reason
Filtering against a calculated aggregate (e.g. above avg salary)	Subquery	Aggregates cannot be placed directly in WHERE without subqueries or HAVING.
Displaying columns from both tables in the final result	JOIN	JOIN combines columns horizontally side-by-side cleanly.
Semi-join existence checks (e.g. customers with orders)	EXISTS / JOIN	EXISTS prevents duplicate rows when one customer has 50+ orders.

Subquery vs. CTE (Common Table Expressions)

While subqueries are written nested inside parentheses, CTEs (Common Table Expressions) define named temporary tables at the very top using the WITH keyword. CTEs make deeply nested queries much easier to read and reuse.

Common Subquery Mistakes to Avoid

1. Returning Multiple Rows to '='

Using = (SELECT ...) when the subquery can return 2+ rows. Always use IN for multi-row outputs.

2. Forgetting Alias on FROM Subqueries

Omitting the table alias (e.g. ) AS derived_table) in the FROM clause causes a syntax error.

3. NOT IN with NULLs Trap

If an inner NOT IN query contains a single NULL, the outer condition evaluates to UNKNOWN and returns 0 rows. Use NOT EXISTS instead.

4. Over-Nesting Simple Logic

Writing 5 levels of deeply nested subqueries when a clean JOIN or CTE would be simpler and faster.

Practical Subquery Exercises

Exercise 1: Second Highest Salary Without LIMIT

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

Exercise 2: Customers with Zero Orders (Inactive Users)

SELECT id, name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
);

⚡ Live Interactive SQL Subquery Practice Lab

Test scalar comparisons, multi-row IN lookups, EXISTS boolean evaluations, and row-by-row correlated loops

Executed Query

SELECT id, name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Inner Query Output: AVG(salary)

$78,333

Dynamic company average

Outer Query Result

2 Qualified

Earning > $78,333

id	Employee Name	Department	Salary	Comparison Result
1	Rahul Sharma	Engineering	$95,000	✓ $95,000 > $78,333
2	Amit Verma	Sales	$55,000	✕ Below Average
3	Priya Patel	Engineering	$110,000	✓ $110,000 > $78,333
4	Neha Singh	Marketing	$65,000	✕ Below Average
5	Rohan Gupta	Sales	$75,000	✕ Below Average
6	Vikram Joshi	Engineering	$70,000	✕ Below Average

SQL Subquery Best Practices

Always Alias Derived Tables in FROM: Standard SQL requires an explicit name for inline subqueries.
Use IN for Multi-Row Lists: Avoid = when inner queries might return more than one row.
Prefer EXISTS over IN When NULLs May Exist: Protect queries from the dangerous NOT IN NULL trap.
Use CTEs for Multi-Level Nested Queries: Improve query readability and maintainability across complex reports.

What You Should Know Now: Checklist

✓Mental Model: Outer query consumes intermediate results produced by the inner query.
✓Scalar Subquery: Returns 1 row, 1 column for direct comparison operators (=, >).
✓Multi-Row IN: Matches candidate rows against a list of returned IDs.
✓EXISTS: Efficiently verifies if at least 1 matching row exists.
✓Correlated Subqueries: Dynamically evaluate row-by-row using outer column references.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL Subqueries

Test your understanding of scalar vs multi-row subqueries, IN vs EXISTS, derived tables, correlated loops, and Subquery vs JOIN tradeoffs.

Question 1 of 8Current Score: 0 / 0

What is the primary role of a subquery in SQL?