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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×