SQL Subqueries Explained — Types, Real-World Patterns and Pitfalls

-- ==================================================
-- Setup: an e-commerce database with two tables
-- ==================================================

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100),
    country       VARCHAR(50)
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    order_total  DECIMAL(10, 2),
    order_date   DATE
);

INSERT INTO customers VALUES
    (1, 'Alice Mercer',   'USA'),
    (2, 'Bruno Klass',    'Germany'),
    (3, 'Carmen Diaz',    'Spain'),
    (4, 'David Park',     'USA'),
    (5, 'Elena Russo',    'Italy');

INSERT INTO orders VALUES
    (101, 1, 250.00, '2024-01-10'),
    (102, 1, 540.00, '2024-02-15'),
    (103, 2, 89.99,  '2024-01-20'),
    (104, 3, 720.50, '2024-03-05'),
    (105, 4, 310.00, '2024-03-18'),
    (106, 4, 95.00,  '2024-04-01'),
    (107, 5, 1200.00,'2024-04-12');

-- ==================================================
-- POSITION 1: Subquery in the WHERE clause
-- Goal: Find customers who placed at least one order
--       above the overall average order value.
-- ==================================================

-- Step 1 (what the subquery does on its own):
-- SELECT AVG(order_total) FROM orders;  --> returns 600.78

SELECT DISTINCT
    c.customer_name,
    c.country
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_total > (
    SELECT AVG(order_total)   -- subquery calculates the average first
    FROM orders               -- across ALL orders in the table
);
-- The outer query then filters rows where order_total beats that average.

-- ==================================================
-- POSITION 2: Subquery in the FROM clause (derived table)
-- Goal: Show each customer's total spend, but only
--       for customers who spent more than $400 overall.
-- ==================================================

SELECT
    customer_summary.customer_id,
    c.customer_name,
    customer_summary.total_spent
FROM (
    -- This inner SELECT produces a temporary result set.
    -- We must give it an alias so the outer query can reference it.
    SELECT
        customer_id,
        SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_summary                          -- <-- alias is mandatory here
JOIN customers c ON c.customer_id = customer_summary.customer_id
WHERE customer_summary.total_spent > 400;

-- ==================================================
-- POSITION 3: Subquery in the SELECT clause
-- Goal: List every customer alongside the number of
--       orders they've placed (even if it's zero).
-- ==================================================

SELECT
    c.customer_name,
    c.country,
    (
        SELECT COUNT(*)           -- scalar subquery: must return exactly ONE value
        FROM orders o
        WHERE o.customer_id = c.customer_id  -- references the outer query's row
    ) AS order_count              -- label the computed column
FROM customers c
ORDER BY order_count DESC;

-- ==================================================
-- NON-CORRELATED subquery
-- The inner query runs ONCE and returns a fixed list.
-- Goal: Find all orders placed by customers in the USA.
-- ==================================================

SELECT
    o.order_id,
    o.order_total,
    o.order_date
FROM orders o
WHERE o.customer_id IN (
    -- This subquery runs ONCE, returns {1, 4} (USA customer IDs),
    -- and the outer query filters against that fixed set.
    SELECT customer_id
    FROM customers
    WHERE country = 'USA'
);

-- ==================================================
-- CORRELATED subquery
-- The inner query runs FOR EACH ROW in the outer query.
-- Goal: Find orders where the order_total is above
--       that specific customer's personal average.
-- ==================================================

SELECT
    o.order_id,
    o.customer_id,
    o.order_total
FROM orders o
WHERE o.order_total > (
    -- Notice: this references o.customer_id from the OUTER query.
    -- For Alice (customer_id=1), it calculates AVG(250.00, 540.00) = 395.00
    -- For David (customer_id=4), it calculates AVG(310.00, 95.00)  = 202.50
    -- A different result for each row — that's what makes it correlated.
    SELECT AVG(inner_orders.order_total)
    FROM orders inner_orders
    WHERE inner_orders.customer_id = o.customer_id  -- <-- the correlation link
);

-- ==================================================
-- EXISTS: The best friend of the correlated subquery
-- Goal: Find customers who have placed at least one order.
-- EXISTS stops as soon as it finds the first matching row.
-- Far more efficient than COUNT(*) > 0 for large tables.
-- ==================================================

SELECT
    c.customer_name,
    c.country
FROM customers c
WHERE EXISTS (
    SELECT 1                        -- SELECT 1 is convention: we don't need the data,
    FROM orders o                   -- we just need to know a matching row exists.
    WHERE o.customer_id = c.customer_id
);

-- ==================================================
-- PATTERN 1: Most recent order per customer
-- Classic 'top N per group' — subquery approach
-- ==================================================

SELECT
    o.customer_id,
    c.customer_name,
    o.order_id,
    o.order_total,
    o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date = (
    -- For THIS customer, find their latest order date.
    -- The correlation is on customer_id — inner changes per row.
    SELECT MAX(latest.order_date)
    FROM orders latest
    WHERE latest.customer_id = o.customer_id
)
ORDER BY o.customer_id;


-- ==================================================
-- PATTERN 2: NOT EXISTS — find customers with NO orders
-- (Safer than NOT IN when NULLs might be present — see Gotchas)
-- ==================================================

SELECT
    c.customer_name,
    c.country
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    -- If zero rows match, NOT EXISTS is TRUE and the customer is included.
);


-- ==================================================
-- PATTERN 3: Inline aggregation as a derived table
-- Goal: Show customers ranked by their total spend,
--       including those with zero orders (spend = 0).
-- ==================================================

SELECT
    c.customer_name,
    c.country,
    COALESCE(spend.total_spent, 0) AS total_spent,  -- COALESCE handles NULLs for non-buyers
    CASE
        WHEN COALESCE(spend.total_spent, 0) >= 1000 THEN 'VIP'
        WHEN COALESCE(spend.total_spent, 0) >= 400  THEN 'Regular'
        ELSE 'New'
    END AS customer_tier
FROM customers c
LEFT JOIN (
    -- Derive per-customer totals once, then join them in.
    -- More efficient than a correlated subquery in SELECT for large sets.
    SELECT
        customer_id,
        SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS spend ON spend.customer_id = c.customer_id
ORDER BY total_spent DESC;