CASE

An expression that returns different values based on conditions. It brings the functionality of if and switch statements from general-purpose programming languages into SQL.

Syntax

-- 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;

Syntax Reference

Syntax	Description
CASE WHEN condition THEN value	Specifies the value to return when the condition is true. You can add multiple WHEN clauses to handle additional conditions.
ELSE value	Specifies the value to return when none of the WHEN conditions match. If omitted, NULL is returned.
END	Marks the end of the CASE expression. It is always required.
CASE column WHEN value	A simple CASE expression that compares a column's value directly. Equivalent to `WHEN column = value` in a searched CASE.

Sample Code

-- Label each employee's salary level (searched CASE).
SELECT name, salary,
    CASE
        WHEN salary >= 400000 THEN 'High'
        WHEN salary >= 250000 THEN 'Standard'
        ELSE 'Review needed'
    END AS salary_level
FROM employees;

-- Convert department codes to department names (simple CASE).
SELECT name,
    CASE dept_code
        WHEN 1 THEN 'Sales'
        WHEN 2 THEN 'Engineering'
        WHEN 3 THEN 'General Affairs'
        ELSE 'Other'
    END AS department
FROM employees;

-- Combine CASE with aggregation to pivot department headcounts into columns.
SELECT
    SUM(CASE WHEN department = 'Sales' THEN 1 ELSE 0 END) AS sales,
    SUM(CASE WHEN department = 'Engineering' THEN 1 ELSE 0 END) AS development,
    SUM(CASE WHEN department = 'General Affairs' THEN 1 ELSE 0 END) AS general_affairs
FROM employees;

Result

-- Example result of: SELECT name, salary, CASE ... END AS salary_level FROM employees;
-- +----------+--------+--------------+
-- | name     | salary | salary_level |
-- +----------+--------+--------------+
-- | Tanaka   | 450000 | High         |
-- | Suzuki   | 280000 | Standard     |
-- | Sato     | 200000 | Review needed|
-- +----------+--------+--------------+

Database Compatibility

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;

Notes

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.

If you find any errors or copyright issues, please contact us.

Home

SQL Dictionary