Learn about SQL CASE with the help of examples. Learn how to use the SQL CASE statement to evaluate conditions and return different results based on the outcome.
The SQL CASE
statement is used to check conditions and perform tasks on each row while selecting data.
-- add a new column named 'Priority' in the output
-- and store 'Huge Order' where amount is greater than or equal to 10000
SELECT order_id, item, amount,
CASE
WHEN amount >= 10000 THEN 'Huge Order'
END AS Priority
FROM Orders;
The SQL CASE
statement has the following syntax:
SELECT column1, column2,... ,
CASE
WHEN condition THEN result
END AS Alias
FROM table;
Here,
column1,column2, ...
are the names of the columns to be included in the result setCASE
checks the condition
result
is the result or value to be inserted to the new column if condition
is satisfiedEND
ends the CASE
statementAS Alias
specifies the Alias
name for the new columntable
is the name of the table.Note: The syntax of CASE
always starts with the CASE
keyword and ends with the END
keyword followed by a column name alias.
-- add a new column 'can_vote' to Customers table
-- insert 'Allowed' into it if customer is older than 17
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. The result set contains
Example: CASE in SQL
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 400 or more.
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 equal to amount - amount*10/100
.
It is also possible to stack multiple conditions inside a single CASE
clause.
-- multiple CASE conditions in SQL
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 the 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.
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,
-- CASE condition with ELSE clause in SQL
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 country_name will be:
ELSE
clause).Example: CASE With ELSE in SQL
#sql