SQL CASE Statements

1. Syntax of CASE Statement

The CASE statement can be written in two forms: simple and searched.

Simple CASE Statement Syntax

Edit Copy

SELECT column1,
       CASE column2
           WHEN value1 THEN result1
           WHEN value2 THEN result2
           ...
           ELSE resultN
       END AS alias
FROM table_name;

Searched CASE Statement Syntax

Edit Copy

SELECT column1,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ...
           ELSE resultN
       END AS alias
FROM table_name;

2. Simple CASE Statement Example

Consider a products table with the following columns: id, name, category_id, and price. We want to categorize products based on their price.

Edit Copy

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

This query retrieves product names and their corresponding categories based on the category_id.

3. Searched CASE Statement Example

Now, let’s assume we want to assign a discount based on the price of each product. We can use a searched CASE statement for this.

Edit Copy

SELECT name, price,
       CASE
           WHEN price < 100 THEN '10% Discount'
           WHEN price BETWEEN 100 AND 500 THEN '20% Discount'
           ELSE 'No Discount'
       END AS discount
FROM products;

This query will return the product name, price, and the applicable discount based on the price range.

4. Using CASE in ORDER BY Clause

You can also use the CASE statement in an ORDER BY clause to sort results conditionally.

Edit Copy

SELECT name, price
FROM products
ORDER BY
       CASE
           WHEN price < 100 THEN 1
           WHEN price BETWEEN 100 AND 500 THEN 2
           ELSE 3
       END;

This query orders products by price ranges, grouping cheaper products first.

5. Conclusion

The CASE statement is an essential tool for adding conditional logic in SQL queries. It enhances your ability to retrieve and manipulate data dynamically based on conditions, making your SQL queries more powerful and flexible.