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:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

You can also use it inside a SELECT statement like this:

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:

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:

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:

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:

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

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:

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:

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:

CASE column_name
  WHEN 'X' THEN 'Result A'
  WHEN 'Y' THEN 'Result B'
  ELSE 'Other'
END

Searched CASE evaluates full conditions:

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:

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:

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:

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:

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.