SQL Tutorial for Beginners: SQL CASE

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.

Example

-- 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;

SQL CASE Syntax

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 set
CASE checks the condition
result is the result or value to be inserted to the new column if condition is satisfied
END ends the CASE statement
AS Alias specifies the Alias name for the new column
table 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.

Example 1: SQL CASE

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

columns with customer_id and first_name with their values
Allowed in the can_vote column for rows whose age is greater than or equal to 18.

How to use CASE in SQL

Example: CASE in SQL

Example 2: 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 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.

SQL CASE With Multiple Conditions

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.

SQL CASE With ELSE

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:

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

#sql