Sql Case

SQL case

Created By: chatGPT

SQL CASE is a powerful tool that allows you to perform conditional logic within your SQL queries. It enables you to create conditional statements that return specific values based on certain criteria. The CASE statement can be used in both SELECT and UPDATE commands to handle various scenarios. Here's how it works:

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

In the CASE statement, you define multiple conditions. Each condition is assessed in the order specified, and the first condition that evaluates to TRUE will dictate the result returned. If none of the conditions are met, the ELSE clause will provide the default result if it’s specified.

SELECT
    first_name,
    last_name,
    CASE
        WHEN age < 18 THEN 'Minor'
        WHEN age >= 18 AND age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM
    users;

You can also use the CASE statement with UPDATE commands. For instance, you can update records in a table based on certain criteria derived from other columns. This allows for dynamic updates depending on the existing data.

UPDATE
    products
SET
    stock_status = CASE
        WHEN quantity > 0 THEN 'In Stock'
        WHEN quantity = 0 THEN 'Out of Stock'
        ELSE 'Unknown'
    END;

Additionally, the CASE statement can be nested within itself for more complex conditions. Just ensure that each CASE is correctly closed to avoid syntax errors. Here is how you can nest CASE statements:

SELECT
    product_name,
    CASE
        WHEN category = 'Electronics' THEN
            CASE
                WHEN stock > 50 THEN 'Available'
                ELSE 'Limited Stock'
            END
        ELSE 'Different Category'
    END AS status
FROM
    products;