Why CASE expressions are needed in SQL

Introduction

CASE expressions let you choose different results based on conditions, like making decisions in your data queries.

You want to show different labels for data based on values, like 'High', 'Medium', or 'Low' prices.

You need to calculate a new column that changes depending on other column values.

You want to replace NULL or missing values with a default text or number.

You want to group data into categories directly in your query output.

You want to apply simple if-then-else logic inside your SELECT or WHERE clauses.

Syntax

SQL

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

The CASE expression checks conditions in order and returns the result for the first true condition.

If no conditions are true, it returns the ELSE result; if ELSE is missing, it returns NULL.

Examples

Assign letter grades based on numeric scores.

SQL

SELECT
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'F'
  END AS grade
FROM students;

Show availability status based on stock quantity.

SQL

SELECT
  product_name,
  CASE
    WHEN stock = 0 THEN 'Out of stock'
    ELSE 'Available'
  END AS availability
FROM products;

Use CASE with simple equality checks to map department IDs to names.

SQL

SELECT
  employee_name,
  CASE department_id
    WHEN 1 THEN 'HR'
    WHEN 2 THEN 'Sales'
    ELSE 'Other'
  END AS department_name
FROM employees;

Sample Program

This query categorizes sales amounts into High, Medium, or Low using CASE.

SQL

CREATE TABLE sales (
  id INT,
  amount INT
);

INSERT INTO sales VALUES (1, 150), (2, 75), (3, 30);

SELECT
  id,
  amount,
  CASE
    WHEN amount >= 100 THEN 'High'
    WHEN amount >= 50 THEN 'Medium'
    ELSE 'Low'
  END AS amount_category
FROM sales;

Important Notes

CASE expressions help you avoid multiple queries or complicated joins for simple conditional logic.

They work inside SELECT, WHERE, ORDER BY, and other SQL clauses.

Remember to test your conditions order carefully because SQL stops checking after the first true condition.

Summary

CASE expressions let you add decision-making inside SQL queries.

They return different results based on conditions you define.

Use CASE to simplify queries that need conditional logic.