SQL CASE Expression

 The CASE expression in SQL is a way to implement conditional logic within queries. It allows you to evaluate conditions and return specific values based on those conditions. There are two types of CASE expressions: Simple CASE and Searched CASE.

1. Simple CASE Expression

The simple CASE expression compares an expression to a set of values and returns a corresponding result for the first match. Here's the syntax:

SELECT column1,
       CASE column2
           WHEN value1 THEN result1
           WHEN value2 THEN result2
           ELSE result3
       END AS result_column
FROM table_name;

Example (Simple CASE):

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

In this example, based on the category_id, a corresponding category_name is returned.

2. Searched CASE Expression

The searched CASE expression evaluates each condition separately and returns a result for the first condition that is TRUE. Here's the syntax:

SELECT column1,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result3
       END AS result_column
FROM table_name;

Example (Searched CASE):

SELECT employee_name,
       CASE
           WHEN salary > 50000 THEN 'High Salary'
           WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS salary_category
FROM employees;

In this example, the salary is evaluated with different conditions, and based on the value, a corresponding salary_category is returned.

Key Points:

• CASE expressions can be used in SELECT, UPDATE, DELETE, ORDER BY, and WHERE clauses.
• The ELSE clause is optional. If not provided and no conditions are met, NULL is returned.

Let me know if you need more examples or have any specific questions!