SQL CASE WHEN THEN — Conditional Logic in Queries

Conditional Logic with CASE

The CASE expression is SQL's if-else construct. It evaluates conditions in order and returns the value of the first matching WHEN branch.

Searched CASE (most common)

SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 70000  THEN 'Mid'
    WHEN salary >= 40000  THEN 'Junior'
    ELSE 'Intern'
  END AS level
FROM employees;

Simple CASE

SELECT name, department_id,
  CASE department_id
    WHEN 1 THEN 'Engineering'
    WHEN 2 THEN 'Marketing'
    WHEN 3 THEN 'Sales'
    ELSE 'Other'
  END AS department_name
FROM employees;

CASE in WHERE Clause

SELECT * FROM orders
WHERE CASE
  WHEN status = 'urgent' THEN priority > 5
  ELSE priority > 2
END;

CASE in ORDER BY

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

CASE in UPDATE

UPDATE employees
SET bonus = CASE
  WHEN rating >= 5 THEN salary * 0.20
  WHEN rating >= 3 THEN salary * 0.10
  ELSE salary * 0.05
END;

Conditional Aggregates

SELECT
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count,
  SUM(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_total
FROM accounts;

The CASE expression is one of the most versatile SQL constructs. It works in SELECT, WHERE, ORDER BY, GROUP BY, UPDATE, and INSERT statements.