🚀 Data Analyst Roadmap — Part 15

🗄️ SQL — Level 5: Subqueries, CTEs & Derived Tables

You've now learned how to retrieve, filter, aggregate, categorize, and join data.

The next step is learning how to break complex SQL problems into smaller, manageable steps.

The three important concepts in this part are:

• Subqueries

• CTEs (Common Table Expressions)

• Derived Tables

These are heavily used in real-world SQL analysis and interviews.

1️⃣ What Is a Subquery?

A subquery is a SQL query inside another SQL query.

Think of it as:

First solve one problem → then use that result to solve another problem.

For example, suppose you want to find employees earning more than the average salary.

First, calculate the average:

SELECT AVG(Salary)
FROM Employees;

Then use that result to filter employees:

SELECT
    Name,
    Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

The query inside the parentheses is the subquery.

2️⃣ Why Use Subqueries?

Without a subquery, you might have to calculate the average separately and manually enter it.

With a subquery, SQL calculates it dynamically.

This is useful for questions such as: Employees earning above average, Products selling above average, Customers spending more than average, Orders larger than the overall average, Finding records based on another query's result

3️⃣ How a Subquery Works

Consider:

SELECT
    Name,
    Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

Conceptually: Subquery ↓ Calculate average salary ↓ Average Salary ↓ Main Query ↓ Find employees above average

The inner query provides a value that the outer query uses.

4️⃣ Scalar Subquery

A scalar subquery returns a single value.

For example:

SELECT AVG(Salary)
FROM Employees;

returns one value.

You can use it like:

SELECT
    Name,
    Salary,
    Salary - (
        SELECT AVG(Salary)
        FROM Employees
    ) AS Difference_From_Average
FROM Employees;

Now every employee can be compared against the overall average.

5️⃣ Subquery with IN

A subquery doesn't always return one value. It can return a list.

Suppose you want customers who have placed at least one order.

SELECT
    Customer_ID,
    Customer_Name
FROM Customers
WHERE Customer_ID IN (
    SELECT Customer_ID
    FROM Orders
);

The inner query returns a list of customer IDs. The outer query retrieves matching customers.

6️⃣ NOT IN

You can also find records that aren't present in another query.

For example:

Find customers who have never placed an order.

SELECT
    Customer_ID,
    Customer_Name
FROM Customers
WHERE Customer_ID NOT IN (
    SELECT Customer_ID
    FROM Orders
);

However, be careful when using NOT IN if the subquery can contain NULL, because NULL semantics can produce unexpected results.

For anti-matching logic, NOT EXISTS or a properly structured LEFT JOIN ... IS NULL is often safer.

7️⃣ EXISTS

EXISTS checks whether a matching row exists.

For example:

SELECT
    c.Customer_ID,
    c.Customer_Name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.Customer_ID = c.Customer_ID
);

This means:

Return customers for whom at least one matching order exists.

You don't need the subquery to return the actual order details. You're simply checking whether a match exists.

8️⃣ NOT EXISTS

NOT EXISTS does the opposite.