CASE Expressions

A case expression in SQL is a conditional expression that enables conditional logic in a SQL statement and the creation of various output values depending on various conditions.

The syntax for a simple case expression is as follows:

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END

The value you wish to test is the expression in this expression. The possible values that the expression can have are condition1, condition2, etc. The values that will be returned if the expression equals the specified value are result1, result2, etc. If none of the value expressions meet the expression, the default_result value will be returned.

SELECT product_name, CASE WHEN price > 100 THEN 'Expensive' WHEN price > 50 THEN 'Moderate' ELSE 'Cheap' END AS price_category FROM products;

In this sentence, a new column called price_category is created using a CASE expression and the product_name column from the products table. A product is labelled as "Expensive" if its price is greater than 100. 'Moderate' is the category for prices that are larger than 50 but less than or equal to 100. A product is labelled as "Cheap" if its price is 50 or less.

More examples of how case expressions can be used in SQL:

1. Using a simple case expression to categorize data

SELECT product_name, CASE category_id WHEN 1 THEN 'Electronics' WHEN 2 THEN 'Clothing' WHEN 3 THEN 'Books' ELSE 'Other' END AS category FROM products;

The category_id column is utilised in this example to categorise the products. The category_id values are mapped to the respective category names using the simple case expression.

2. Using a searched case expression to apply conditional logic

SELECT customer_name, CASE WHEN purchase_total > 1000 THEN 'Platinum' WHEN purchase_total > 500 THEN 'Gold' ELSE 'Silver' END AS customer_type FROM customers;

In this illustration, the customer_type is ascertained based on the purchase_total using the searched case expression. Customers who have made more than $1,000 in purchases are classed as Platinum, those who have made more than $500 in purchases are classified as Gold, and the remainder customers are classified as Silver.

3. Using a nested case expression to apply conditional logic

SELECT order_id, CASE WHEN ship_date IS NOT NULL THEN CASE WHEN ship_date > required_date THEN 'Late' ELSE 'On Time' END ELSE 'Not Shipped' END AS status FROM orders;

In this illustration, the status of an order is ascertained using the nested case expression. A second level of conditional logic is utilized to assess whether the order is late or on time if the ship_date is not null. The status is set to Not Shipped if the ship_date is nil.