Day 26 – SQL CASE Expression
SQL CASE expression is a conditional expression used to perform a variety of operations in SQL queries, including conditional logic, data transformations, and aggregations. It is similar to the IF-THEN-ELSE construct found in many programming languages.
The syntax for the SQL CASE expression is as follows:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
WHEN valueN THEN resultN
ELSE default_result
END
In this syntax, the expression is evaluated and compared against each value in the WHEN clause. If a match is found, the corresponding result is returned. If no matches are found, the default_result is returned.
Here’s an example of how the SQL CASE expression can be used in a query:
Suppose you have a table named “orders” that contains information about customer orders, including order ID, customer ID, and order amount. You want to create a new column in the table called “order_status” that indicates whether each order is above or below a certain threshold amount.
SELECT order_id, customer_id, order_amount,
CASE
WHEN order_amount > 1000 THEN 'Above Threshold'
ELSE 'Below Threshold'
END AS order_status
FROM orders;
In this example, the CASE expression is used to create a new column named “order_status” that indicates whether each order is above or below a threshold of $1000. If an order has an order amount greater than 1000, the “Above Threshold” string is returned. Otherwise, the “Below Threshold” string is returned. The resulting query output will contain the original columns from the “orders” table, as well as the new “order_status” column indicating whether each order is above or below the threshold.