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:
- 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
- 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:
- "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.
Searched CASE Expression Syntax
- The syntax for a searched CASE expression is:
- 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:
- 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: