Topic 22 of 52 · Intermediate
CASE, Functions & UNION
Conditional logic, string/date functions and set operators.
Concept
CASE WHEN adds if/else logic inside a query — for labels, buckets and conditional aggregates (pivots).
Common functions: CONCAT, UPPER, SUBSTRING, TRIM, DATE_FORMAT, DATEDIFF, DATE_ADD, YEAR, IFNULL.
UNION combines results and removes duplicates; UNION ALL keeps them and is faster. Columns must match in count and type.
Code (SQL · MySQL 8)
SELECT name,
CASE WHEN amount >= 5000 THEN 'High'
WHEN amount >= 1000 THEN 'Medium'
ELSE 'Low' END AS bucket
FROM orders o JOIN customers c ON c.id = o.customer_id;
-- Pivot: orders per status per month
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(status = 'delivered') AS delivered,
SUM(status = 'cancelled') AS cancelled
FROM orders GROUP BY month;Remember
- SUM(CASE ...) / SUM(condition) = pivot.
- UNION ALL unless you need de-duplication.
- Wrapping indexed columns in functions blocks index use.
Interview questions
UNION vs UNION ALL?
UNION removes duplicate rows (extra sort/hash work). UNION ALL keeps all rows and is faster.
How do you turn rows into columns?
Conditional aggregation: SUM(CASE WHEN col = 'x' THEN val END) for each target column, grouped by the row key.
Lesson test
Take an 8-question test on “CASE, Functions & UNION” — concepts, output prediction, bug spotting and interview questions.