SQL CASE

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).