Searched CASE syntax in SQL

Introduction

The searched CASE syntax helps you choose different results based on conditions. It works like asking multiple questions and giving answers depending on which question is true.

You want to show different labels for numbers, like grades or scores.

You need to group data into categories based on ranges, like age groups.

You want to replace codes with readable text in reports.

You want to create new columns that depend on multiple conditions.

You want to simplify complex if-else logic inside a query.

Syntax

SQL

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

Each WHEN is a condition you check one by one.

The ELSE part is optional and used if no conditions match.

Examples

This example assigns letter grades based on score ranges.

SQL

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

This example shows stock status based on the number of items left.

SQL

SELECT
  product_name,
  CASE
    WHEN stock = 0 THEN 'Out of stock'
    WHEN stock < 5 THEN 'Low stock'
    ELSE 'In stock'
  END AS stock_status
FROM products;

Sample Program

This query creates a table of employees with salaries. Then it selects each employee's name, salary, and assigns a salary level based on ranges using searched CASE.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  salary INT
);

INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 70000),
(2, 'Bob', 48000),
(3, 'Charlie', 52000),
(4, 'Diana', 30000);

SELECT
  name,
  salary,
  CASE
    WHEN salary >= 60000 THEN 'High'
    WHEN salary >= 40000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_level
FROM employees;

Important Notes

Conditions in WHEN are checked in order. The first true condition stops the checking.

You can use any valid condition, like comparisons or logical expressions.

ELSE is optional. If missing and no condition matches, the result is NULL.

Summary

Searched CASE lets you return different results based on multiple conditions.

It works like if-else but inside SQL queries.

Use it to make your query results easier to read and understand.