SQL CASE Statement

The SQL CASE statement is used to add conditional logic inside SQL queries. It checks conditions one by one and returns a value as soon as a matching condition is found.

• Works like an IF-THEN-ELSE statement inside SQL.
• Helps categorize data or transform values dynamically.
• Can be used in SELECT, UPDATE, ORDER BY and other clauses.

Example: First, we create a demo SQL table, on which we use the CASE statement.

Query:

SELECT CustomerID, CustomerName, Age,
CASE Age
 WHEN 21 THEN 'Young Adult'
 WHEN 22 THEN 'Adult'
 WHEN 23 THEN 'Mid Adult'
 WHEN 24 THEN 'Senior Adult'
 ELSE 'Unknown'
END AS AgeGroup
FROM Customer;

Output:

Syntax:

CASE case_value
 WHEN value1 THEN result1
 WHEN value2 THEN result2
 ...
 ELSE result
END

• Compares a column or expression with fixed values.
• Returns the result of the first matching value.

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

• Evaluates multiple logical conditions.
• Returns the result of the first true condition.

Working with the CASE Statement

The SQL CASE statement allows conditional logic in queries by returning different values based on specified conditions.

Example 1: Simple CASE Expression

This demonstrates how conditional rules are applied to return a nationality based on each customer’s country.

Query:

SELECT CustomerName, Country, Age,
CASE
 WHEN Country = 'United Kingdom' THEN 'British'
 WHEN Country = 'Australia' THEN 'Australian'
 WHEN Country = 'Japan' THEN 'Japanese'
 WHEN Country = 'Austria' THEN 'Austrian'
 WHEN Country = 'Spain' THEN 'Spanish'
 ELSE 'Other'
END AS Nationality
FROM Customer;

Output:

Example 2: SQL CASE When Multiple Conditions

We can add multiple conditions in the CASE statement by using multiple WHEN clauses.

Query:

SELECT CustomerName, Age,
CASE
 WHEN Age = 21 THEN 'Age is 21'
 WHEN Age = 22 THEN 'Age is 22'
 WHEN Age > 22 THEN 'Age is greater than 22'
 ELSE 'Age is below 21'
END AgeDescription
FROM Customer;

Output:

Example 3: CASE Statement With ORDER BY Clause

The Customer table contains fields like CustomerID, CustomerName, LastName, Country, Age and Phone and its data can be arranged using the ORDER BY clause together with the CASE statement.

Query:

SELECT
 CustomerName,
 Country,
 CASE
 WHEN Country = 'Japan' THEN 0
 ELSE 1
 END AS SortPriority
FROM Customer
ORDER BY SortPriority, Country;

Output: