SQL CASE Expression for Conditional Logic

SQL CASE Expression for Conditional Logic

The CASE expression provides if-then-else logic within SQL queries. It can appear in SELECT, WHERE, ORDER BY, GROUP BY, and HAVING clauses, making it one of the most versatile SQL constructs.

Simple CASE

Compares a single expression to multiple values:

SELECT name, status,
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    WHEN 'P' THEN 'Pending'
    ELSE 'Unknown'
  END AS status_label
FROM users;

Searched CASE

Evaluates multiple independent conditions:

SELECT name, salary,
  CASE
    WHEN salary >= 120000 THEN 'Senior'
    WHEN salary >= 80000 THEN 'Mid-Level'
    WHEN salary >= 50000 THEN 'Junior'
    ELSE 'Entry Level'
  END AS salary_band
FROM employees;

Conditions are evaluated top-to-bottom; the first match wins.

CASE in ORDER BY

SELECT * FROM tickets
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    WHEN 'low' THEN 4
    ELSE 5
  END ASC;

CASE in Aggregations (Pivot Queries)

SELECT
  department,
  COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
  COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
  COUNT(CASE WHEN gender NOT IN ('M', 'F') THEN 1 END) AS other_count
FROM employees
GROUP BY department;

This pivots row data into columns without using database-specific PIVOT syntax.

CASE with NULL

SELECT name,
  CASE WHEN phone IS NULL THEN 'No phone' ELSE phone END AS phone_display
FROM contacts;
-- Equivalent to: COALESCE(phone, 'No phone')

Best Practices

• Always include an ELSE clause to handle unexpected values (defaults to NULL if omitted)
• Keep CASE expressions readable; extract very complex logic into views or computed columns
• Use COALESCE or NULLIF instead of CASE for simple NULL handling
• CASE is an expression (returns a value), not a control flow statement
• Using CASE in WHERE can prevent index usage; prefer rewriting as multiple OR conditions when possible

CASE expressions enable powerful data transformations and conditional logic directly within queries, reducing the need for post-processing in application code.