SQL CASE Expression: Syntax, Usage, and Examples
The SQL CASE expression lets you perform conditional logic within queries. You can return different values depending on the evaluation of a condition. Think of it as SQL’s version of if-else, useful when you want custom labels, grouped values, or transformations inside SELECT, UPDATE, or even ORDER BY clauses.
How to Use the SQL CASE Expression
The basic structure of a SQL CASE expression looks like this:
sql
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
You can also use it inside a SELECT statement like this:
sql
SELECT
name,
salary,
CASE
WHEN salary > 70000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees;
This query categorizes each employee’s salary. The SQL case expression checks each condition in order and returns the first match. If no condition matches, it uses the ELSE value.
When to Use a SQL CASE Expression
Replace Numeric or Coded Values With Meaningful Labels
You might store status codes as numbers, but users prefer readable labels:
sql
SELECT
order_id,
CASE status
WHEN 0 THEN 'Pending'
WHEN 1 THEN 'Shipped'
WHEN 2 THEN 'Delivered'
ELSE 'Unknown'
END AS order_status
FROM orders;
This version of a case expression in SQL converts codes into something people understand.
Group or Bucket Data
You can classify data into groups, like income ranges or age brackets:
sql
SELECT
name,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM customers;
This makes reporting and visualizations easier by categorizing data.
Create Custom Sorting Logic
Let’s say you want a certain set of values to appear in a specific order:
sql
SELECT name, priority
FROM tasks
ORDER BY
CASE priority
WHEN 'High' THEN 1
WHEN 'Medium' THEN 2
WHEN 'Low' THEN 3
ELSE 4
END;
Here, the SQL case expression controls the sort order based on values—not alphabetical order.
Apply Conditional Logic in Updates
You can use CASE in an UPDATE statement too:
sql
UPDATE employees
SET bonus =
CASE
WHEN performance_rating = 'Excellent' THEN 1000
WHEN performance_rating = 'Good' THEN 500
ELSE 0
END;
This updates bonus amounts based on performance evaluations in a single query.
Examples of SQL CASE Expression in Action
Example 1: CASE Expression in SELECT Statement
sql
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM students;
This is one of the most common uses of the SQL case expression: turning scores into letter grades.
Example 2: CASE With Multiple Columns
You can combine conditions across columns:
sql
SELECT employee_id,
CASE
WHEN department = 'Sales' AND salary > 60000 THEN 'Top Sales'
WHEN department = 'HR' AND years_of_service > 10 THEN 'Veteran HR'
ELSE 'General Staff'
END AS role_tag
FROM employees;
This version allows complex logic to decide what value to return.
Example 3: CASE in WHERE Clause Alternative
Although CASE doesn’t directly work in a WHERE clause, you can use it creatively in filters:
sql
SELECT *
FROM orders
WHERE
(CASE
WHEN shipping_country = 'USA' THEN 1
WHEN shipping_country = 'Canada' THEN 1
ELSE 0
END) = 1;
You’re filtering based on multiple values with a custom logic layer.
Learn More About SQL CASE Expression
Simple vs Searched CASE Expressions
There are two forms of the CASE expression in SQL:
Simple CASE matches a column to multiple values:
sql
CASE column_name
WHEN 'X' THEN 'Result A'
WHEN 'Y' THEN 'Result B'
ELSE 'Other'
END
Searched CASE evaluates full conditions:
sql
CASE
WHEN column_name > 100 THEN 'High'
WHEN column_name BETWEEN 50 AND 100 THEN 'Medium'
ELSE 'Low'
END
The searched version is more flexible and widely used.
CASE Expression SQL Formatting Tips
Always write CASE expressions with clear indentation. Each WHEN should be on a new line, and the END keyword should align with the CASE. This makes your logic easier to read and debug.
Use CASE to Prevent NULL Errors
You can use CASE to handle NULLs:
sql
SELECT name,
CASE
WHEN email IS NULL THEN 'No Email'
ELSE email
END AS contact_info
FROM users;
This prevents unexpected NULLs from affecting your reports.
Combine CASE With Aggregates
You can count or sum conditionally:
sql
SELECT
COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_users,
COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_users
FROM users;
Now you can run grouped logic inside aggregation functions like COUNT or SUM.
CASE Inside Nested Queries
Use SQL case expressions inside subqueries to filter or label data before further processing:
sql
SELECT name, status_label
FROM (
SELECT name,
CASE
WHEN status = 1 THEN 'Open'
WHEN status = 2 THEN 'Closed'
ELSE 'Other'
END AS status_label
FROM tickets
) AS t
WHERE status_label != 'Other';
This approach keeps logic modular and easy to reuse.
CASE for Error Handling or Validation
Use SQL case expression logic to flag invalid or unusual data:
sql
SELECT id,
CASE
WHEN LENGTH(phone_number) < 10 THEN 'Invalid Number'
ELSE 'OK'
END AS phone_check
FROM contacts;
You can highlight or fix problems as part of your query logic.
CASE and Performance Considerations
Using SQL case expressions in SELECT statements is typically efficient, but adding them to ORDER BY or complex JOINs can slow things down. Keep your expressions short and indexed where possible. If you find yourself writing long CASE chains, it might be time to consider restructuring your data or using a lookup table.
The SQL CASE expression is one of the most versatile tools in your SQL toolkit. You can return custom labels, group results, apply conditional logic, and improve the readability of your queries.