🚀 SQL Roadmap 2026 — Part 12

🧩 SQL Subqueries — Using One Query Inside Another

So far, we've learned how to retrieve, filter, group, transform, and combine data.

But sometimes a business question requires one query to use the result of another query.

For example:

«Find employees whose salary is higher than the average salary.»

First, we need to calculate:

SELECT AVG(salary)
FROM employees;

Then compare every employee against that result.

A subquery allows us to do both inside one SQL statement.

🧠 1. What Is a Subquery?

A subquery is a SQL query written inside another SQL query.

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

The inner query:

SELECT AVG(salary) FROM employees

calculates the average salary. The outer query then uses that result.

Think of it as:

Outer Query → Needs an answer → Subquery calculates the answer → Outer Query uses it

🔹 2. Basic Subquery Structure

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT ...
);

The inner query is enclosed in ( ... ).

📊 3. Subquery Returning One Value

A scalar subquery returns a single value.

SELECT
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query returns something like 65000. The outer query then finds employees earning more than 65000.

💰 4. Employees Earning Above Average

Classic interview question.

SELECT
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Logic: Calculate average salary → Compare every employee → Keep salary > average

🏆 5. Products More Expensive Than Average

SELECT
    product_name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

🔢 6. Subquery with COUNT()

Customers who have placed more than 5 orders:

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
);

🔗 7. Subquery with IN

Useful when a subquery returns multiple values.

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

Returns customers who have at least one order.

⚠️ 8. Single Value vs Multiple Values

One value → Use =, >, <, >=, <=

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

Multiple values → Use IN

WHERE customer_id IN (
    SELECT customer_id
    FROM orders
)

🚫 9. NOT IN

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

⚠️ Important NULL Warning: NOT IN can produce unexpected results if the subquery contains NULL. For anti-matching, NOT EXISTS is often safer.

🔎 10. EXISTS

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

Means: Return the customer if at least one matching order exists.

🚫 11. NOT EXISTS

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