Case Statement in SQL - sql - sql tutorial - learn sql



  • CASE is used in providing 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.
 process of case statement

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 statement is optional in a CASE expression.
  • When one is not defined, and no matches are made, NULL is returned.
  • If an ELSE is defined, and no matches are made, then “otherResult” is returned.
  • The ELSE clause is a great way to catch bad or unexpected data values, and return a result other than NULL.
 case statements of sorting process

Simple CASE Expression Example

  • We use the following table for our example.
  • Table Store_Information
Store_Name Sales Txn_Date
Alabama 1500 March-06-2017
Ohio 250 March -08-2017
Texas 300 March-09-2017
Wyoming 700 March-09-2017
  • To multiply the sales amount from ' Alabama ' by 2 and the sales amount from ' Ohio ' 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 ' Alabama ' THEN Sales * 2
  WHEN ' Ohio ' 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, ' Alabama ' and ' Ohio ', are static values.

Result:

Store_Name New Sales Txn_Date
Alabama 3000 Jan-05-2017
Ohio 375 Jan-07-2017
Texas 300 Jan-08-2017
Wyoming 700 Jan-08-2017

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:
    • 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_Name Txn_Date Sales Status
Alabama Jan-05-2017 Good Day
Ohio Jan-07-2017 Bad Day
Texas Jan-08-2017 Bad Day
Wyoming Jan-08-2017 OK 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 'Alabama' THEN Sales * 2
  WHEN 'Ohio' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Txn_Date
FROM Store_Information;

Searched CASE Expression:

SELECT Store_Name, CASE
  WHEN Store_Name = ' Alabama ' THEN Sales * 2
  WHEN Store_Name = ' Ohio ' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Txn_Date
FROM Store_Information;

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Case Statement in SQL