CASE Statements in SQL

CASE statements in SQL very helpful when it comes to the problem where result depends on a specific condition that could be apply to column. CASE statement helps to create Derive column that means you will take existing columns and modify it. By using CASE statement it is the way to perform “IF” “THEN” logic in SQL. The most important tips for using CASE statemen are following:

• The CASE statement always goes in the SELECT clause.
• CASE must include the following components: WHEN, THEN, and END. ELSE is an optional component to catch cases that didn’t meet any of the other previous CASE conditions.
• You can make any conditional statement using any conditional operator (like WHERE) between WHEN and THEN. This includes stringing together multiple conditional statements using AND and OR.
• You can include multiple WHEN statements, as well as an ELSE statement again, to deal with any unaddressed conditions.

One of the most common practice to use CASE statement when task require classify entities in the table into general groups based on specific criterion.

Query solution for this task and result table shown below.

CASE statements becoming also very useful with aggregate functions. For example, using previous table orders we would like to count how many orders belong to each group. Lets create 2 groups only and find out how it works.

Aggregating based on new categories will make it easier to generate appropriate reports. For the next example lets use 2 different tables to perform more complex task.

Lets create 3 different levels of customers based on the amount associated with their purchases. The top level includes anyone with a total sales of all orders greater than 200,000 USD. The second level is between 200,000 and 100,000 USD. The lowest level is anyone under 100,000 USD. Lets create a table that includes the level associated with each account. Table should include the account name, the total sales of all orders for the customer, and the level. Lets sort table with the top spending customers listed first.

Conclusion

CASE statements in SQL comes very helpful for a verity of complex tasks. But we should always remember some rules related to it:

• SQL CASE statements support up to 10 levels of nesting
• You cannot control the flow of executions of the statements, functions or procedures using CASE expressions
• You should always use an ELSE block so that if any conditions are not satisfied, you get a default value
• You should avoid using conflicting conditions in the SQL CASE statement. The CASE statement works sequentially and stops evaluating with the first successful condition