SQL CASE

CASE is used to provide if-then-else type of logic to SQL. There are two formats: The first is a Simple CASE expression, where we compare an expression to static values. The second is a Searched CASE expression, where we compare an expression to one or more logical conditions.

Simple CASE Expression Syntax

The syntax for a simple CASE expression is:

SELECT CASE ("column_name")
  WHEN "value1" THEN "result1"
  WHEN "value2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name";

The ELSE clause is optional.

Simple CASE Expression Example

We use the following table for our example.

Table Store_Information

To multiply the sales amount from 'Los Angeles' by 2 and the sales amount from 'San Diego' by 1.5 while keeping the sales amount for other stores the same, we would use the following SQL statement using CASE:

SELECT Store_Name, CASE Store_Name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Txn_Date
FROM Store_Information;

"New Sales" is the name given to the column with the CASE statement. This is an example of a simple CASE expression, because the conditions listed, 'Los Angeles' and 'San Diego', are static values.

Result:

Searched CASE Expression Syntax

The syntax for a searched CASE expression is:

SELECT CASE
  WHEN "condition1" THEN "result1"
  WHEN "condition2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name";

The ELSE clause is optional. "Condition" can consist of one or more logical statements.

Searched CASE Expression Example

We use the same Store_Information above. If we want to define the status of a store's sale based on the following rules:

SELECT Store_Name, Txn_Date, CASE
  WHEN Sales >= 1000 THEN 'Good Day'
  WHEN Sales >= 500 THEN 'OK Day'
  ELSE 'Bad Day'
  END
"Sales Status"
FROM Store_Information;

SELECT Store_Name, CASE Store_Name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Txn_Date
FROM Store_Information;

SELECT Store_Name, CASE
  WHEN Store_Name = 'Los Angeles' THEN Sales * 2
  WHEN Store_Name = 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Txn_Date
FROM Store_Information;