SQL CASE WHEN, UNION vs UNION ALL and Pivot Queries

CASE WHEN is SQL's if-else: it turns values into categories, counts rows conditionally and builds pivot reports. This guide covers CASE, combining result sets with UNION and UNION ALL, and pivot queries that turn rows into columns.

CASE WHEN Expression

-- Salary band classification
SELECT name, salary,
    CASE
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 60000  THEN 'Mid-Level'
        ELSE                       'Junior'
    END AS seniority_band
FROM employees;

-- CASE in aggregate functions (gender pivot)
SELECT dept,
    COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
    COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
    COUNT(*) AS total
FROM employees GROUP BY dept;

-- CASE in ORDER BY (custom priority sort)
SELECT name, status FROM orders
ORDER BY CASE status
    WHEN 'urgent'  THEN 1
    WHEN 'pending' THEN 2
    WHEN 'shipped' THEN 3
    ELSE 4
END;

UNION and UNION ALL

-- UNION ALL (faster — use when no duplicates expected)
SELECT order_id, customer_id, amount FROM orders_2024
UNION ALL
SELECT order_id, customer_id, amount FROM orders_2023;

-- INTERSECT — rows in BOTH queries (PostgreSQL/Oracle)
SELECT customer_id FROM jan_orders INTERSECT SELECT customer_id FROM feb_orders;

-- EXCEPT/MINUS — rows in first but NOT second (PostgreSQL/Oracle)
SELECT customer_id FROM jan_orders EXCEPT SELECT customer_id FROM feb_orders;

-- Simulate INTERSECT in MySQL with INNER JOIN
SELECT DISTINCT a.customer_id FROM jan_orders a
JOIN feb_orders b ON a.customer_id = b.customer_id;

Pivot Queries with CASE WHEN

-- Monthly sales by category — conditional aggregation pivot
SELECT category,
    SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS Feb,
    SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS Mar,
    SUM(CASE WHEN MONTH(order_date) = 4 THEN amount ELSE 0 END) AS Apr,
    SUM(CASE WHEN MONTH(order_date) = 5 THEN amount ELSE 0 END) AS May,
    SUM(amount) AS Year_Total
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE YEAR(order_date) = 2025
GROUP BY category ORDER BY Year_Total DESC;

FAQs

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows (which needs a sort or hash step); UNION ALL keeps every row and is faster. Use UNION ALL unless you need de-duplication.

Can CASE WHEN be used inside aggregate functions?

Yes, for conditional aggregation: SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END), which is also how pivot reports are built.

What happens if no CASE condition matches?

The result is the ELSE value, or NULL if there is no ELSE.