How to Use CASE Expressions in SQL

Posted by Kyle Hankinson November 18, 2020

---

The CASE expression lets you add conditional logic directly in a SQL query. It works like an IF/THEN/ELSE statement and is supported by every major database.

Searched CASE

The most common form evaluates conditions in order and returns the first match:

SELECT name, score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

Conditions are evaluated top to bottom. Once a match is found, the rest are skipped.

Simple CASE

When comparing a single column to specific values, use the simple form:

SELECT order_id, status,
    CASE status
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_label
FROM orders;

CASE in WHERE and ORDER BY

CASE is not limited to the SELECT list:

-- Custom sort order
SELECT * FROM tasks
ORDER BY
    CASE priority
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
        ELSE 5
    END;

Conditional Aggregation

One of the most powerful uses — pivot-style queries without a PIVOT operator:

SELECT
    department,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
FROM employees
GROUP BY department;

Database-Specific Shortcuts

Some databases offer shorter alternatives for simple conditions:

-- MySQL
SELECT IF(score >= 60, 'Pass', 'Fail') FROM students;

-- SQL Server
SELECT IIF(score >= 60, 'Pass', 'Fail') FROM students;

-- Oracle
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM users;

These are convenient but not portable. CASE works everywhere.

Handling NULLs

CASE does not match NULLs with =. Use IS NULL:

SELECT name,
    CASE
        WHEN phone IS NULL THEN 'No phone on file'
        ELSE phone
    END AS contact_phone
FROM customers;

For simple NULL replacement, COALESCE is shorter:

SELECT name, COALESCE(phone, 'No phone on file') AS contact_phone
FROM customers;

---