For an instructor lead, in-depth look at learning SQL click below.
The SQL CASE statement is a conditional construct that allows for decision making in SQL queries. In other words, the CASE statement allows you to write if-then-else kind of logic in your SQL. Through this post, we’ll delve deeper into its purpose utility by exploring usage examples.
The Basic Syntax of the CASE Statement
The CASE statement is quite versatile and fitting in a variety of situations. Here’s its basic syntax:
|
1 2 3 4 5 6 7 8 9 |
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END; |
In the above SQL, the CASE statement evaluates the conditions one by one from the top. The first TRUE condition will return the result. If no condition is TRUE, the result in the ELSE branch is returned.
Usage Example #1: CASE in SELECT statement
Here is an example where the CASE statement is used in a SELECT statement:
|
1 2 3 4 5 6 7 8 9 |
SELECT OrderID, CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30' WHEN Quantity = 30 THEN 'The quantity is 30' ELSE 'The quantity is under 30' END AS QuantityText FROM OrderDetails; |
In this example, we introduced a new column “QuantityText”, this column will hold values depending on the Quantity column’s value in each row.
Usage Example #2: CASE in UPDATE statement
Another example, this time we will use the CASE statement to decide what to set a column value to during an UPDATE.
|
1 2 3 4 5 6 7 8 |
UPDATE Table SET state = CASE WHEN state = 'NY' THEN 'New York' WHEN state = 'CA' THEN 'California' ELSE state END; |
In this UPDATE operation, the CASE statement is used to determine what value to set the state column to. Depending on the current state column’s value, it changes ‘NY’ to ‘New York’ and ‘CA’ to ‘California’. If the state column’s current value is neither ‘NY’ nor ‘CA’, then it keeps the same value.
Conclusion
The CASE statement is an important tool in SQL. Its ability to handle conditional logic within SQL statements makes it crucial for complex database interactions. While we discuss two examples here, the CASE statement has a broad range of uses and is advantageous in many other instances.