Means: Return customers for whom no matching order exists.

🧠 12. EXISTS vs IN

IN → Compare against a set of returned values

EXISTS → Check whether a matching row exists

🔄 13. Correlated Subquery

Depends on the current row of the outer query.

SELECT
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

«Is this employee's salary higher than the average salary of their own department?»

🏢 14. Above-Department-Average Salary

This is a correlated subquery. Bob is compared against the Sales average, while David is compared against the IT average.

📦 15. Subquery in FROM

It can appear in the FROM clause.

SELECT
    customer_id,
    total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_sales;

This is often called a derived table.

📈 16. Finding High-Value Customers

SELECT
    customer_id,
    total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_sales
WHERE total_spending > 50000;

🧩 17. Subquery in SELECT

SELECT
    c.customer_name,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.customer_id
    ) AS order_count
FROM customers c;

⚠️ 18. But Be Careful with Correlated Subqueries

Instead of a correlated subquery in SELECT, you could use:

SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

The better choice depends on the database optimizer, table size, indexes, and other factors.

🧱 19. Nested Subqueries

SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category_id IN (
        SELECT category_id
        FROM categories
        WHERE category_name = 'Electronics'
    )
);

Deeply nested queries can become difficult to read. Use CTEs instead.

🧠 20. Subqueries vs JOINs

Subquery:

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

JOIN:

SELECT DISTINCT
    c.*
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Choose based on readability, business logic, and performance.

🏆 21. Subqueries for Business Analysis

Find products above average:

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

Find customers with at least one order:

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

Find customers with more than 10 orders:

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

🧮 22. Subquery for KPI Comparison

SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);