Conditional Logic with CASE in SQL

Conditional Logic with CASE in SQL

The CASE statement in SQL is a powerful tool that allows you to implement conditional logic within your queries. It works similarly to an IF-THEN-ELSE statement in programming languages, allowing you to evaluate conditions and return specific values based on the results of those conditions. The CASE statement can be used in SELECT, UPDATE, and ORDER BY clauses, among others, making it highly versatile.

1. Understanding the CASE Statement

The CASE statement evaluates a series of conditions and returns a value based on the first condition that evaluates to TRUE. If no conditions are met, an optional ELSE clause can provide a default value.

Syntax:

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

  • condition1, condition2, ...: Conditions to be evaluated in sequence.

  • result1, result2, ...: Values to be returned if the corresponding condition is TRUE.

  • default_result: A value to be returned if none of the conditions are met. This is optional.

  • alias_name: An alias for the result of the CASE statement.

2. Basic Usage of CASE in a SELECT Statement

The most common use of CASE is within a SELECT statement to create new columns or modify existing ones based on conditional logic.

Example:

SELECT first_name, last_name, salary,
       CASE
           WHEN salary > 60000 THEN 'High'
           WHEN salary BETWEEN 40000 AND 60000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

In this example:

  • The CASE statement categorizes employees' salaries into High, Medium, and Low based on the value of the salary column.

  • The result of the CASE statement is given the alias salary_category.

Result:

first_name
last_name
salary
salary_category

Amit

Sharma

65000.00

High

Neha

Singh

45000.00

Medium

Ravi

Verma

35000.00

Low

In this output:

  • Employees are categorized into different salary ranges based on the conditions specified in the CASE statement.

3. Using CASE in WHERE Clause

You can also use the CASE statement in the WHERE clause to create dynamic filtering conditions.

Example:

SELECT first_name, last_name, department
FROM employees
WHERE 
    CASE 
        WHEN department = 'Sales' THEN salary > 50000
        WHEN department = 'IT' THEN salary > 60000
        ELSE TRUE
    END;

In this example:

  • The CASE statement is used to apply different salary filters depending on the department.

  • For employees in Sales, only those with a salary greater than 50,000 are selected.

  • For employees in IT, the salary filter is set to 60,000.

4. Using CASE in ORDER BY Clause

The CASE statement can also be used in the ORDER BY clause to control the sorting of the result set based on conditions.

Example:

SELECT first_name, last_name, department, salary
FROM employees
ORDER BY 
    CASE
        WHEN department = 'Sales' THEN salary
        WHEN department = 'IT' THEN first_name
        ELSE last_name
    END ASC;

In this example:

  • The CASE statement determines the sorting order based on the department.

  • If the department is Sales, the result set is sorted by salary.

  • If the department is IT, the result set is sorted by first name.

  • For other departments, the result set is sorted by last name.

5. Using CASE in UPDATE Statement

You can also use the CASE statement in an UPDATE query to conditionally update rows in a table.

Example:

UPDATE employees
SET bonus = 
    CASE
        WHEN department = 'Sales' THEN salary * 0.10
        WHEN department = 'IT' THEN salary * 0.15
        ELSE salary * 0.05
    END;

In this example:

  • The CASE statement is used to update the bonus column based on the employee's department.

  • Employees in Sales receive a bonus of 10% of their salary, while those in IT receive 15%. All other departments receive a 5% bonus.

6. Using Nested CASE Statements

You can nest CASE statements inside each other to handle more complex conditions.

Example:

SELECT first_name, last_name, salary,
       CASE
           WHEN department = 'Sales' THEN 
               CASE
                   WHEN salary > 60000 THEN 'High Sales'
                   ELSE 'Low Sales'
               END
           WHEN department = 'IT' THEN 
               CASE
                   WHEN salary > 60000 THEN 'High IT'
                   ELSE 'Low IT'
               END
           ELSE 'Other Department'
       END AS salary_category
FROM employees;

In this example:

  • The outer CASE statement evaluates the department, and the inner CASE statements further categorize employees based on their salary within each department.

Result:

first_name
last_name
salary
salary_category

Amit

Sharma

65000.00

High Sales

Neha

Singh

55000.00

Low IT

Ravi

Verma

35000.00

Other Department

In this output:

  • Employees are categorized based on both their department and salary.

7. Handling NULL Values with CASE

CASE can be used to handle NULL values by explicitly checking for NULL in the conditions.

Example:

SELECT first_name, last_name, department,
       CASE
           WHEN department IS NULL THEN 'No Department'
           ELSE department
       END AS department_name
FROM employees;

In this example:

  • The CASE statement checks if the department column is NULL and replaces it with "No Department" if it is.

Result:

first_name
last_name
department
department_name

Amit

Sharma

Sales

Sales

Neha

Singh

NULL

No Department

In this output:

  • NULL values in the department column are replaced with "No Department".

8. Using CASE with Aggregations

You can use CASE statements in combination with aggregation functions like SUM, COUNT, or AVG to perform conditional aggregations.

Example:

SELECT department,
       SUM(CASE WHEN salary > 50000 THEN salary ELSE 0 END) AS high_salary_total,
       SUM(CASE WHEN salary <= 50000 THEN salary ELSE 0 END) AS low_salary_total
FROM employees
GROUP BY department;

In this example:

  • The CASE statement is used to separate the salaries into two categories (high_salary_total and low_salary_total) based on whether they are above or below 50,000.

  • The SUM function then aggregates the salaries within each category.

Result:

department
high_salary_total
low_salary_total

Sales

65000.00

0.00

IT

0.00

45000.00

In this output:

  • The salaries are aggregated separately based on the CASE statement conditions.

Conclusion

The CASE statement in SQL is a versatile and powerful tool that allows you to implement conditional logic directly within your queries. Whether you need to create conditional columns, filter rows based on complex criteria, or apply dynamic sorting and updates, mastering the CASE statement is essential for advanced SQL querying. With the ability to handle multiple conditions, work with NULL values, and even nest CASE statements, you can create highly flexible and dynamic SQL queries to meet a wide range of requirements.

This tutorial is brought to you by codes with pankaj.

PreviousNULL Handling in SQLNextSQL Joins

Last updated