Conditional Logic using CASE

tags: #sql/complex_queries

The CASE keyword is used in SQL to perform conditional logic by defining a set of conditions and corresponding actions to be taken based on whether those conditions are satisfied.

This is essentially analogous to the IF-THEN-ELSE statement in Python. It allows you to execute different code blocks based on specified conditions.

General Syntax:

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

In Python:

if condition:
    # Code to be executed if the condition is True
else:
    # Code to be executed if the condition is False

Unlike in Python where only the de-indentation is necessary to signal the end of the conditional code block, we start with CASE and end the condition using END.

WHEN Clause

Used to specify the condition

THEN Clause

Used to specify the action to take if said condition specified by the WHEN clause is satisfied

ELSE Clause

The ELSE clause provides a default result to be returned if none of the conditions are met.

Use-cases

1. SELECT Statement

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

• The CASE statement categorizes the products based on their prices into 'Cheap', 'Moderate', or 'Expensive', and assigns the category name to a new column called 'price_category'.
  
  
• The SELECT statement returns the product name, price, and price category for all products in the 'products' table.

2. UPDATE Statement

The CASE statement can also be used with the UPDATE statement to conditionally update values in a table based on certain criteria.

UPDATE table_name
SET column_name =
    CASE
        WHEN condition1 THEN value1
        WHEN condition2 THEN value2
        ELSE default_value
    END
WHERE condition;

3. INSERT INTO Statement

CASE can be used with INSERT INTO statement to insert data into a table based on certain conditions.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
  (value1, 
   CASE
     WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     WHEN condition3 THEN result3
     ELSE default_result
   END,
   value3, ...);

// example
INSERT INTO employees (id, name, salary, employment_status)
VALUES (1, 'John Doe', 60000, 
        CASE // conditonal logic of employment_status based on salary
            WHEN salary >= 50000 THEN 'full-time' 
            ELSE 'part-time' 
        END);