SQL CASE

SQL CASE is a conditional statement that allows you to perform different actions based on different conditions. The CASE statement can be used in various parts of a SQL query, such as in the SELECT, WHERE, and ORDER BY clauses.

The basic syntax of a SQL CASE statement is as follows:

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

Here, condition1, condition2, and so on represent the conditions that you want to evaluate, result1, result2, and so on represent the results that you want to return if the corresponding condition is true, and resultN represents the default result to return if none of the conditions are true.

For example, the following statement uses the CASE statement in the SELECT clause to categorize products based on their unit price:

SELECT product_name, unit_price,
       CASE
           WHEN unit_price < 10 THEN 'Low Price'
           WHEN unit_price >= 10 AND unit_price < 50 THEN 'Medium Price'
           ELSE 'High Price'
       END AS price_category
FROM products;

In this example, the CASE statement categorizes products into three price categories based on their unit price. The result set includes the product name, unit price, and price category.

You can also use a searched CASE statement, which evaluates a series of Boolean conditions, like this:

SELECT customer_id,
       CASE
           WHEN country = 'USA' AND state = 'CA' THEN 'West Coast'
           WHEN country = 'USA' AND state = 'NY' THEN 'East Coast'
           WHEN country = 'USA' THEN 'Other'
           ELSE 'International'
       END AS location
FROM customers;

In this example, the CASE statement categorizes customers based on their location. The result set includes the customer ID and location.

The SQL CASE statement can be a powerful tool for manipulating and analyzing data, allowing you to create customized output based on specific conditions.