The CASE statement in SQL is used to evaluate a series of conditions and returns the values when the first condition is met. It is very similar to if-else statements. As the first condition came out to be TRUE, it will stop the process and will ignore the other conditions. If no part of the IF condition is true then it will get parsed to the ELSE condition and returns the value associated with it.
If no conditions are TRUE, then it will return NULL as its result.
- Syntax of CASE Statement –
CASE
WHEN [Condition1] THEN [Result Expression1]
WHEN [Condition2] THEN [Result Expression2]
WHEN [Condition3] THEN [Result Expression3]
.....
WHEN [ConditionN] THEN [Result ExpressionN]
ELSE [Result Expression] END;
Demo Table –
Product Table
| Category | Region | City | Country | Customer_ID | Customer_name | Sales | Profit |
| Office Supplies | Central | Chicago | United States | SM-20950 | Suzanne McNair | 15.696 | 5.1012 |
| Technology | East | New York City | United States | AH-10465 | Amy Hunt | 539.97 | 134.992 |
| Office Supplies | East | New York City | United States | AH-10465 | Amy Hunt | 22.58 | 5.8708 |
| Office Supplies | East | Dover | United States | EP-13915 | Emily Phan | 16.52 | 7.5992 |
| Office Supplies | East | Dover | United States | EP-13915 | Emily Phan | 671.94 | 315.8118 |
| Technology | West | Aurora | United States | TP-21565 | Tracy Poddar | 169.064 | -14.7931 |
| Office Supplies | West | Aurora | United States | TP-21565 | Tracy Poddar | 168.624 | 14.7546 |
| Office Supplies | East | Long Beach | United States | AR-10825 | Anthony Rawles | 19.44 | 9.3312 |
| Office Supplies | West | Pasadena | United States | HA-14920 | Helen Andreada | 31.92 | 8.2992 |
| Furniture | West | Pasadena | United States | HA-14920 | Helen Andreada | 433.568 | -65.0352 |
Example – Consider the Product table of the Customer database, and show the profit status as Profit or Loss. When the profit is greater than ‘0’ call it Profit and when the Profit is lesser than ‘0’ replace it with Loss.
SELECT Category,Region,City,
[Country/Region],[Customer ID],[Customer Name],Sales,Profit,
CASE WHEN [Profit]>0 THEN 'Profit'
WHEN [Profit]< 0 THEN 'Loss'
ELSE 'No Loss No Profit'
END AS Status
FROM [dbo].[Products];
The output shows the execution of the CASE Statement, and also has an alias for the Case statement as Status –
| Category | Region | City | Country | Customer_ID | Customer_name | Sales | Profit | Status |
| Office Supplies | Central | Chicago | United States | SM-20950 | Suzanne McNair | 15.696 | 5.1012 | Profit |
| Technology | East | New York City | United States | AH-10465 | Amy Hunt | 539.97 | 134.9925 | Profit |
| Office Supplies | East | New York City | United States | AH-10465 | Amy Hunt | 22.58 | 5.8708 | Profit |
| Office Supplies | East | Dover | United States | EP-13915 | Emily Phan | 16.52 | 7.5992 | Profit |
| Office Supplies | East | Dover | United States | EP-13915 | Emily Phan | 671.94 | 315.8118 | Profit |
| Technology | West | Aurora | United States | TP-21565 | Tracy Poddar | 169.064 | -14.7931 | Loss |
| Office Supplies | West | Aurora | United States | TP-21565 | Tracy Poddar | 168.624 | 14.7546 | Profit |
| Office Supplies | East | Long Beach | United States | AR-10825 | Anthony Rawles | 19.44 | 9.3312 | Profit |
| Office Supplies | West | Pasadena | United States | HA-14920 | Helen Andreada | 31.92 | 8.2992 | Profit |
| Furniture | West | Pasadena | United States | HA-14920 | Helen Andreada | 433.568 | -65.0352 | Loss |
Other SQL topics to check out:
- What is RDBMS (Relational Database Management System)?
- SQL SELECT Statement
- SQL INSERT INTO SELECT Statement
- SQL ALL Operator
- SQL WHERE Clause
- SQL AND Operator
- SQL OR Operator
Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
