← SQL roadmap

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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “CASE, Functions & UNION” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.