SQL CASE Tutorial

The SQL CASE statement is used to implement conditional logic in SQL queries. It allows you to perform different actions based on specific conditions. This tutorial will guide you through the syntax and usage of the CASE statement with practical examples.

Syntax

The basic syntax of the CASE statement is as follows:

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

• condition1, condition2, …: These are the conditions that are evaluated in order.
• result1, result2, …: The result returned if the corresponding condition is true.
• default_result: The result returned if none of the conditions are true (optional).

Example 1: Simple CASE

Let’s say you have a table named employees with columns id, name, and salary. You want to categorize employees based on their salary:

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

This query will return the name, salary, and a new column salary_category that categorizes each employee’s salary as ‘High’, ‘Medium’, or ‘Low’.

Example 2: CASE in ORDER BY

You can also use the CASE statement in the ORDER BY clause to sort results based on a conditional logic:

SELECT 
    name,
    salary
FROM employees
ORDER BY 
    CASE
        WHEN salary > 100000 THEN 1
        WHEN salary BETWEEN 50000 AND 100000 THEN 2
        ELSE 3
    END;

This query will sort employees with high salaries first, followed by medium and low salaries.

Example 3: CASE in UPDATE

The CASE statement can also be used in an UPDATE statement to conditionally update records:

UPDATE employees
SET salary = 
    CASE
        WHEN salary < 50000 THEN salary * 1.1
        WHEN salary BETWEEN 50000 AND 100000 THEN salary * 1.05
        ELSE salary
    END;

This query will increase the salary of employees by 10% if their salary is less than 50,000, by 5% if their salary is between 50,000 and 100,000, and leave it unchanged otherwise.

Conclusion

The SQL CASE statement is a powerful tool for implementing conditional logic in your queries. It can be used in SELECT, UPDATE, ORDER BY, and other clauses to make your SQL queries more flexible and dynamic. Practice using the CASE statement with different scenarios to fully grasp its potential.