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

Simple CASE Expression

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.
For example, assume we have the following Store_Information table,
Table Store_Information
Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

if we want 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:

Store_NameNew SalesTxn_Date
Los Angeles3000Jan-05-1999
San Diego375Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

Searched CASE Expression

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.
If we want to define the status of a store's sale based on the following rules:
  • If Sales >= 1,000, it's a "Good Day"
  • If Sales >= 500 and < 1,000, it's an "OK Day"
  • If Sales < 500, it's a "Bad Day"We can use the following searched CASE expression:

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

    Store_NameTxn_DateSales Status
    Los AngelesJan-05-1999Good Day
    San DiegoJan-07-1999Bad Day
    San FranciscoJan-08-1999Bad Day
    BostonJan-08-1999OK Day
    Note that a simple CASE expression is a special case of a searched CASE expression. As an example, the following two CASE expressions are identical:
    Simple CASE Expression:

    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;
    Searched CASE Expression:

    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;