Sql When

SQL when

Created By: chatGPT

The WHEN clause in SQL is commonly used within a CASE statement to allow you to perform conditional logic directly within your queries. It helps you create more dynamic and responsive SQL queries based on your data. The CASE statement can be thought of as similar to an IF statement in programming languages.

SELECT
    column1,
    column2,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE default_result
    END AS new_column
FROM table_name;

In the example above, the CASE statement checks for multiple conditions evaluated in sequence. The first condition that is true will determine the value assigned to new_column. If none of the conditions are met, the ELSE clause is executed, assigning default_result.

SELECT
    product_name,
    stock,
    CASE
        WHEN stock > 50 THEN 'In Stock'
        WHEN stock BETWEEN 1 AND 50 THEN 'Low Stock'
        ELSE 'Out of Stock'
    END AS stock_status
FROM products;

The WHEN clause can be particularly useful for creating custom views or reports that require status updates based on certain thresholds or ranges. Keep in mind that you can also nest CASE statements for more complex logic if needed.

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary < 30000 THEN 'Low'
        WHEN salary BETWEEN 30000 AND 70000 THEN 'Average'
        WHEN salary > 70000 THEN
            CASE
                WHEN salary > 100000 THEN 'High'
                ELSE 'Upper Average'
            END
        END AS salary_range
FROM employees;