The CASE
statement in SQL is used to check conditions and perform tasks on each row while selecting data. For example,
SELECT customer_id, first_name,
CASE
WHEN age >= 18 THEN 'Allowed'
END AS can_vote
FROM Customers;
Here, the SQL command checks each row with the given case.
If age is greater than or equal to 18, the result set contains
- columns with customer_id and first_name with their values
- Allowed is returned as a can_vote column.

Note: The syntax of CASE
always starts with the CASE
keyword and ends with the END
keyword followed by column name alias.
Example Two: SQL CASE Statement
Let's take a look at another example where we want to provide a 10% discount on each order for a Christmas sale if the amount is more than 400.
SELECT order_id, customer_id,
CASE
WHEN amount >= 400 THEN (amount - amount * 10/100)
END AS offer_price
FROM Orders;
Here, the SQL command checks if the amount is greater than or equal to 400. If this condition is satisfied, a new column offer_price will contain the values that's equal to amount - amount*10/100
.
Multiple Cases
It is also possible to stack multiple conditions inside the CASE
clause.
SELECT customer_id, first_name,
CASE
WHEN country = 'USA' THEN 'United States of America'
WHEN country = 'UK' THEN 'United Kingdom'
END AS country_name
FROM Customers;
Here, the result set will contain a column named country_name along with customer_id and first_name columns.
The value of country_name will be United States of America if the country is equal to USA.
Similarly, the value of country_name will be United Kingdom if the country is equal to UK.
CASE With ELSE in SQL
A CASE
statement can have an optional ELSE
clause. The ELSE
clause is executed if none of the conditions in the CASE
statement is matched. For example,
SELECT customer_id, first_name,
CASE
WHEN country = 'USA' THEN 'United States of America'
WHEN country = 'UK' THEN 'United Kingdom'
ELSE 'Unknown Country'
END AS country_name
FROM Customers;
Here, the result set will contain a field named country_name along with customer_id and first_name.
The value of the country_name will be:
- United States of America if the country is USA
- United Kingdom if the country is UK
- Unknown Country if the country is neither USA nor UK (because of the
ELSE
clause).
