SQL CASE

In this tutorial, we'll learn about the CASE statement in SQL and how to use them with examples.

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.
How to use CASE in SQL
Example: CASE in SQL

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).
How to use CASE With ELSE in SQL
Example: CASE With ELSE in SQL
Did you find this article helpful?