Subqueries and CASE WHEN

A subquery is a query inside a query. CASE WHEN is SQL's if/else. Together they let you answer questions that a single flat query can't — comparing rows to aggregates, creating conditional buckets, and building multi-step logic in one statement.

Learning Objectives

• Write subqueries in WHERE, FROM, and SELECT
• Use correlated vs. non-correlated subqueries
• Apply CASE WHEN for conditional column values and bucketing
• Understand when to use subqueries vs. CTEs (Common Table Expressions)

---

Subqueries in WHERE

The most common use: filter rows based on a computed value from another query.

Business question: "Which orders have a total above the average order value?"

SELECT order_id, customer_id, order_total
FROM orders
WHERE order_total > (
    SELECT AVG(order_total) FROM orders WHERE status = 'completed'
)
AND status = 'completed'
ORDER BY order_total DESC;

What happens: 1. The inner query runs first: SELECT AVG(order_total) FROM orders WHERE status = 'completed' → returns 268.31 2. The outer query uses that value: WHERE order_total > 268.31

Sample output:

order_id	customer_id	order_total
O002	C003	389.00
O008	C002	389.00
O005	C003	345.50

IN with a subquery

-- Orders from VIP customers only
SELECT order_id, customer_id, order_date, order_total
FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE segment = 'VIP'
)
ORDER BY order_date DESC;

---

Subqueries in FROM (Derived Tables)

A subquery in FROM creates a temporary "derived table" you can query from. This is how you apply aggregation and then filter on the result.

Business question: "Which customers have an average order value above £200?"

SELECT
    customer_summary.customer_id,
    customer_summary.avg_order_value,
    customer_summary.total_orders
FROM (
    SELECT
        customer_id,
        COUNT(*)            AS total_orders,
        AVG(order_total)    AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) AS customer_summary
WHERE customer_summary.avg_order_value > 200
ORDER BY customer_summary.avg_order_value DESC;

For mid-level analysts

Subqueries in FROM are called derived tables. CTEs (with WITH ... AS) do the same thing but are more readable, especially when you need to reference the same result multiple times. Prefer CTEs in production code.

---

Common Table Expressions (CTEs)

CTEs replace deeply nested subqueries with a named, readable block. Same logic, cleaner syntax.

-- CTE version of the query above
WITH customer_summary AS (
    SELECT
        customer_id,
        COUNT(*)            AS total_orders,
        AVG(order_total)    AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    cs.customer_id,
    c.first_name,
    c.last_name,
    cs.avg_order_value,
    cs.total_orders
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
WHERE cs.avg_order_value > 200
ORDER BY cs.avg_order_value DESC;

Sample output:

customer_id	first_name	last_name	avg_order_value	total_orders
C003	Sarah	Johnson	349.50	6
C012	Rohan	Kapoor	285.00	4

Chained CTEs

WITH
-- Step 1: calculate monthly revenue
monthly_revenue AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m')    AS month,
        SUM(order_total)                    AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
-- Step 2: find months above the average monthly revenue
avg_revenue AS (
    SELECT AVG(revenue) AS avg_monthly_revenue FROM monthly_revenue
)
SELECT
    mr.month,
    mr.revenue,
    ar.avg_monthly_revenue,
    mr.revenue - ar.avg_monthly_revenue     AS above_average_by
FROM monthly_revenue mr
CROSS JOIN avg_revenue ar
WHERE mr.revenue > ar.avg_monthly_revenue
ORDER BY mr.month;

---

Subqueries in SELECT (Scalar Subqueries)

A scalar subquery returns exactly one value and is used as a column in SELECT.

-- Show each order alongside the customer's total order count
SELECT
    o.order_id,
    o.customer_id,
    o.order_total,
    (
        SELECT COUNT(*) FROM orders o2
        WHERE o2.customer_id = o.customer_id
          AND o2.status = 'completed'
    )   AS customer_completed_orders
FROM orders o
WHERE o.status = 'completed'
ORDER BY customer_completed_orders DESC;

Correlated subqueries in SELECT can be slow

This subquery runs once per row in the outer query. On 1 million rows, it runs 1 million times. This is a correlated subquery — it references o.customer_id from the outer query. Replace with a JOIN or window function for performance.

Faster rewrite using JOIN:

WITH customer_counts AS (
    SELECT customer_id, COUNT(*) AS completed_orders
    FROM orders WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    o.order_id,
    o.customer_id,
    o.order_total,
    cc.completed_orders AS customer_completed_orders
FROM orders o
JOIN customer_counts cc ON o.customer_id = cc.customer_id
WHERE o.status = 'completed'
ORDER BY cc.completed_orders DESC;

---

CASE WHEN — Conditional Logic

CASE WHEN adds if/else branching directly in SQL.

Simple CASE — equality checks

SELECT
    order_id,
    status,
    CASE status
        WHEN 'completed'    THEN 'Fulfilled'
        WHEN 'pending'      THEN 'In Progress'
        WHEN 'cancelled'    THEN 'Lost Sale'
        ELSE 'Other'
    END     AS status_label
FROM orders;

Searched CASE — any condition

SELECT
    order_id,
    order_total,
    CASE
        WHEN order_total < 50   THEN 'Small'
        WHEN order_total < 200  THEN 'Medium'
        WHEN order_total < 500  THEN 'Large'
        ELSE 'Enterprise'
    END     AS order_size
FROM orders;

Sample output:

order_id	order_total	order_size
O001	298.00	Large
O003	149.97	Medium
O004	35.99	Small
O002	389.00	Large

CASE WHEN with aggregation

-- Revenue by order size bucket
SELECT
    CASE
        WHEN order_total < 50   THEN 'Small (<£50)'
        WHEN order_total < 200  THEN 'Medium (£50-£200)'
        WHEN order_total < 500  THEN 'Large (£200-£500)'
        ELSE 'Enterprise (£500+)'
    END                 AS order_size,
    COUNT(*)            AS order_count,
    SUM(order_total)    AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY
    CASE
        WHEN order_total < 50   THEN 'Small (<£50)'
        WHEN order_total < 200  THEN 'Medium (£50-£200)'
        WHEN order_total < 500  THEN 'Large (£200-£500)'
        ELSE 'Enterprise (£500+)'
    END
ORDER BY total_revenue DESC;

---

Practice Exercises

Warm-up 1. Find all orders with a total greater than the overall average order total. 2. Add a CASE WHEN column to the products table that labels products as 'Budget' (<£50), 'Mid-range' (£50-£150), or 'Premium' (>£150). 3. Write a CTE that calculates total orders per customer, then query from it to find customers with more than 5 orders.

Main 4. Find customers who have ordered more than once. Use a subquery in WHERE. 5. Write a query using a CTE that shows each product's revenue alongside the overall total revenue and the product's percentage share. 6. Using CASE WHEN, create a query that classifies customers as 'High Value' (total spent > £1000), 'Mid Value' (£200-£1000), or 'Low Value' (<£200) based on their total completed order spend.

Stretch 7. Write a query that finds the top-selling product in each category. (Hint: subquery or window function.) 8. Rewrite this correlated subquery using a JOIN + CTE for better performance:

SELECT o.order_id, o.customer_id,
       (SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id) AS last_order_date
FROM orders o;

---

Interview Questions

[Beginner] What is a subquery in SQL?

[Beginner] What does CASE WHEN ... THEN ... END do? Give an example.

[Mid-level] What is the difference between a correlated and a non-correlated subquery?

[Mid-level] You need to find products that have never been ordered. Write this using a subquery and also using a LEFT JOIN.

[Mid-level] When would you use a CTE instead of a subquery?

[Senior] A correlated subquery in the SELECT clause runs in 8 minutes on 5 million rows. How do you fix it?

---

← GROUP BY and HAVING · Next: Window Functions →