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