SQL CASE

What is the SQL CASE expression?

Adds conditional branching logic that returns different values based on evaluated conditions within a SQL statement.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CASE: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2, Teradata

SQL CASE Full Explanation

The CASE expression lets you embed IF-THEN-ELSE style logic directly in SQL. It evaluates conditions in the order written and returns the first matching result; if nothing matches, the optional ELSE branch is used. CASE is not a control-of-flow statement (it does not short-circuit query execution) but an expression that yields a single scalar value, so it can appear wherever a value is allowed: SELECT lists, WHERE clauses, ORDER BY, GROUP BY, HAVING, UPDATE/INSERT/DELETE assignments, and even CHECK constraints.There are two forms:1. Simple CASE compares one expression to a list of potential values.2. Searched CASE evaluates independent Boolean conditions.All result expressions must be type-compatible so the database can determine a single output data type. When multiple WHEN branches match (possible only in searched CASE), the first one wins. NULL comparisons follow normal SQL rules (="NULL" never matches in simple CASE without IS NULL).

SQL CASE Syntax

-- Simple CASE
CASE input_expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    [...]
    [ELSE default_result]
END

-- Searched CASE
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    [...]
    [ELSE default_result]
END

SQL CASE Parameters

input_expression (any scalar) - The expression being compared in a simple CASE.
valueN (same type) - Literal or expression compared to input_expression.
conditionN (boolean) - Predicate evaluated in searched CASE.
resultN (any scalar) - Value returned when its WHEN matches.
default_result (any scalar) - Value returned when no WHEN matches (optional).

Example Queries Using SQL CASE

-- 1. Categorize order size (searched CASE)
SELECT order_id,
       quantity,
       CASE
           WHEN quantity >= 100 THEN 'Large'
           WHEN quantity >= 10  THEN 'Medium'
           ELSE 'Small'
       END AS size_bucket
FROM orders;

-- 2. Map status codes to text (simple CASE)
SELECT user_id,
       CASE status
           WHEN 0 THEN 'Inactive'
           WHEN 1 THEN 'Active'
           WHEN 2 THEN 'Banned'
           ELSE 'Unknown'
       END AS status_text
FROM users;

-- 3. Conditional aggregation
SELECT
  SUM(CASE WHEN paid = true THEN amount ELSE 0 END) AS paid_total,
  SUM(CASE WHEN paid = false THEN amount ELSE 0 END) AS unpaid_total
FROM invoices;

-- 4. Dynamic ordering
SELECT product_name, revenue
FROM products
ORDER BY CASE WHEN revenue > 100000 THEN 0 ELSE 1 END, revenue DESC;

Expected Output Using SQL CASE

Each query returns a result set with new columns whose values depend on evaluated CASE logic
No schema changes are made

Use Cases with SQL CASE

Derive categorical labels from numeric ranges
Replace numeric or coded values with human-readable text
Perform conditional aggregation without multiple passes over data
Implement soft pivots by combining CASE with SUM or MAX
Drive custom sort orders when ORDER BY needs business rules
Prevent division-by-zero by returning NULL or 0 when a denominator is zero

Common Mistakes with SQL CASE

Forgetting END keyword, causing syntax error
Mixing simple and searched forms (providing input_expression then using Boolean conditions)
Omitting ELSE and assuming unmatched rows return NULL
Returning incompatible data types among THEN branches (e.g., INT and VARCHAR)
Expecting CASE to short-circuit expensive functions - most engines still evaluate all branches

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between simple CASE and searched CASE?

Simple CASE compares one expression to several values. Searched CASE evaluates independent Boolean predicates. Choose searched CASE for complex conditions.

Does CASE short-circuit evaluation?

The first matching WHEN decides the returned value, but most engines still compute all branch expressions. Keep heavy calculations outside CASE when possible.

Where can I use CASE in a query?

Anywhere a single scalar value is allowed: SELECT, WHERE, GROUP BY, HAVING, ORDER BY, JOIN conditions, and data-modification statements like UPDATE.

What happens if no WHEN matches and there is no ELSE?

CASE returns NULL, which may propagate through expressions or filter rows out if compared with regular equality (remember to use IS NULL).