CASE

Searched CASE (branches based on a condition expression).

SELECT CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ELSE default_value
END AS alias
FROM table_name;

Simple CASE (branches by comparing a value directly).

SELECT CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_value
END AS alias
FROM table_name;

The following examples use the employees table.

Label each employee's salary level (searched CASE).

sample_case.sql

SELECT name, salary,
    CASE
        WHEN salary >= 400000 THEN 'High'
        WHEN salary >= 250000 THEN 'Standard'
        ELSE 'Review needed'
    END AS salary_level
FROM employees;

+--------+--------+---------------+
| name   | salary | salary_level  |
+--------+--------+---------------+
| user_a | 450000 | High          |
| user_c | 280000 | Standard      |
| user_e | 200000 | Review needed |
+--------+--------+---------------+
3 rows in set

Convert department codes to department names (simple CASE).

sample_case.sql

SELECT name,
    CASE dept_code
        WHEN 1 THEN 'Sales'
        WHEN 2 THEN 'Engineering'
        WHEN 3 THEN 'Admin'
        ELSE 'Other'
    END AS department
FROM employees;

+--------+------------+
| name   | department |
+--------+------------+
| user_a | Engineering   |
| user_c | Sales  |
| user_e | Sales  |
+--------+------------+
3 rows in set

Combine CASE with aggregation to pivot department headcounts by dept_code into columns.

sample_case.sql

SELECT
    SUM(CASE WHEN dept_code = 1 THEN 1 ELSE 0 END) AS dept_1,
    SUM(CASE WHEN dept_code = 2 THEN 1 ELSE 0 END) AS dept_2,
    SUM(CASE WHEN dept_code = 3 THEN 1 ELSE 0 END) AS dept_3
FROM employees;

+--------+--------+--------+
| dept_1 | dept_2 | dept_3 |
+--------+--------+--------+
|      2 |      1 |      0 |
+--------+--------+--------+
1 row in set

The CASE expression is part of standard SQL and works consistently across MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

SELECT name, salary,
    CASE
        WHEN salary >= 400000 THEN 'High'
        WHEN salary >= 250000 THEN 'Standard'
        ELSE 'Review needed'
    END AS salary_level
FROM employees;

The CASE expression can be used anywhere in a query — in the SELECT, WHERE, ORDER BY, and GROUP BY clauses. Because it is an expression that returns a value, you can use it as an argument to aggregate functions or as part of a larger arithmetic expression, just like a column value.

The searched CASE supports any condition, including inequalities and range checks, making it more flexible than the simple CASE. The simple CASE only performs equality (=) comparisons, but it is more readable when you are mapping codes to values.

To replace NULL values with a fallback, you can also use COALESCE / NULLIF.