Simple CASE syntax in SQL

Introduction

The Simple CASE syntax helps you choose a value based on matching one expression to several possible values. It works like a simple decision maker.

You want to show different text based on a status code in a table.

You need to convert numeric codes into readable labels in a report.

You want to assign categories to data based on a single column's value.

You want to replace NULL or unknown values with a default message.

You want to simplify complex IF-THEN-ELSE logic in your query.

Syntax

SQL

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

The expression is checked against each value in order.

If a match is found, the corresponding result is returned.

Examples

This example converts letter grades into descriptive words.

SQL

SELECT
  CASE grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    WHEN 'C' THEN 'Average'
    ELSE 'Needs Improvement'
  END AS performance
FROM students;

This example assigns category names based on category IDs.

SQL

SELECT
  product_id,
  CASE category_id
    WHEN 1 THEN 'Books'
    WHEN 2 THEN 'Electronics'
    ELSE 'Other'
  END AS category_name
FROM products;

Sample Program

This query creates a table of orders with status codes, inserts some data, and then uses Simple CASE to show readable status descriptions.

SQL

CREATE TABLE orders (
  order_id INT,
  status_code INT
);

INSERT INTO orders VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4);

SELECT order_id,
  CASE status_code
    WHEN 1 THEN 'Pending'
    WHEN 2 THEN 'Shipped'
    WHEN 3 THEN 'Delivered'
    ELSE 'Unknown'
  END AS status_description
FROM orders
ORDER BY order_id;

Important Notes

The ELSE part is optional but useful to handle unexpected values.

Simple CASE compares the expression to each WHEN value using equality.

Summary

Simple CASE checks one expression against multiple values.

It returns the result for the first matching value.

Use it to make your queries easier to read and understand.