Conditional Logic with SQL CASE

In SQL, the CASE statement is a powerful tool that allows for conditional logic within queries. It works similarly to an IF-THEN-ELSE structure found in many programming languages, enabling you to perform conditional transformations or calculations directly in your SQL queries. This chapter will explain the concept of the CASE statement from basic to advanced levels, using detailed examples to ensure a comprehensive understanding of how it works.

The SQL CASE statement allows you to add conditional logic to your SQL queries. It enables you to evaluate conditions and return specific values when those conditions are met, making it especially useful for creating new columns based on conditional logic, transforming values, and performing calculations.

Basic Syntax

There are two types of CASE statements in SQL: Simple CASE and Searched CASE.

Simple `CASE` Syntax:

				
					CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END;

expression: The value being compared.
WHEN value: The condition being checked.
THEN result: The result if the condition is true.
ELSE: Optional. This defines the result if no conditions match.

Searched `CASE` Syntax:

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

condition: A logical expression that evaluates to TRUE or FALSE.
THEN: Defines the result for each condition.
ELSE: Optional. This defines the default result if none of the conditions are true.

Simple CASE vs Searched CASE

Simple `CASE`:

The simple CASE evaluates an expression and compares it to predefined values. It’s best when you’re checking equality against specific values.

Example:

				
					SELECT employee_id, 
       name, 
       CASE department_id
           WHEN 1 THEN 'HR'
           WHEN 2 THEN 'Sales'
           WHEN 3 THEN 'IT'
           ELSE 'Other'
       END AS department_name
FROM employees;

Explanation:

The query checks the department_id and assigns department names based on it.
If the department_id is 1, it returns ‘HR’. If it’s 2, it returns ‘Sales’, and so on.

Output:

employee_id	name	department_name
1	Alice	HR
2	Bob	Sales
3	Charlie	IT
4	David	Other

Searched `CASE`:

The searched CASE evaluates conditions. It is more flexible because you can use a wide range of conditions, not just equality checks.

Example:

				
					SELECT employee_id, 
       name, 
       salary, 
       CASE 
           WHEN salary > 50000 THEN 'High Salary'
           WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS salary_range
FROM employees;

Explanation:

This query classifies employees based on their salary range.
Employees earning more than 50,000 get labeled as ‘High Salary’, and so on.

Output:

employee_id	name	salary	salary_range
1	Alice	60000	High Salary
2	Bob	45000	Medium Salary
3	Charlie	25000	Low Salary

Using CASE in SELECT

The CASE statement is commonly used within SELECT queries to transform data.

Example:

				
					SELECT name, 
       salary, 
       CASE 
           WHEN salary >= 50000 THEN 'Above Average'
           ELSE 'Below Average'
       END AS salary_status
FROM employees;

Explanation:

This query categorizes employees based on whether their salary is above or below average.

Output:

name	salary	salary_status
Alice	60000	Above Average
Bob	40000	Below Average
Charlie	55000	Above Average

CASE with Aggregate Functions

You can use CASE statements inside aggregate functions like SUM, COUNT, and AVG to conditionally perform calculations.

Example:

				
					SELECT department_id, 
       SUM(CASE WHEN salary > 50000 THEN salary ELSE 0 END) AS total_high_salary
FROM employees
GROUP BY department_id;

Explanation:

This query calculates the total salary of employees who earn more than 50,000, grouped by department.

Output:

department_id	total_high_salary
1	120000
2	50000

Using CASE in ORDER BY

The CASE statement can also be used to conditionally order query results.

Example:

				
					SELECT name, salary
FROM employees
ORDER BY 
    CASE 
        WHEN salary > 50000 THEN 1
        ELSE 2
    END, salary DESC;

Explanation:

This query first orders the employees by whether their salary is above 50,000 (those who earn more come first), and then by salary in descending order.

Output:

name	salary
Alice	60000
Charlie	55000
Bob	40000

CASE in WHERE Clauses

You can use CASE inside WHERE clauses to apply complex filtering logic.

Example:

				
					SELECT name, salary
FROM employees
WHERE 
    CASE 
        WHEN department_id = 1 THEN salary > 50000
        ELSE salary > 30000
    END;

Explanation:

For employees in department 1, it filters those earning more than 50,000. For others, it filters those earning more than 30,000.

Output:

name	salary
Alice	60000
Bob	45000

Nested CASE Statements

In some cases, you may need to nest CASE statements for more complex logic.

Example:

				
					SELECT name, 
       CASE 
           WHEN salary > 50000 THEN
               CASE 
                   WHEN department_id = 1 THEN 'HR High Earner'
                   ELSE 'Other High Earner'
               END
           ELSE 'Low Earner'
       END AS employee_category
FROM employees;

Explanation:

This query first checks if the employee has a high salary. If true, it further checks the department to assign specific labels.

Output:

name	employee_category
Alice	HR High Earner
Charlie	Other High Earner
Bob	Low Earner

Handling NULL Values with CASE

CASE can also be used to handle NULL values effectively, replacing them with custom values.

Example:

				
					SELECT name, 
       salary, 
       CASE 
           WHEN salary IS NULL THEN 'No Salary Info'
           ELSE salary
       END AS salary_info
FROM employees;

Explanation:

This query replaces NULL salary values with the string ‘No Salary Info’.

Output:

name	salary_info
Alice	60000
Bob	No Salary Info
Charlie	55000

Advanced Use Cases

`CASE` for Data Transformation:

You can use CASE to perform complex data transformations in one query.

Example:

				
					SELECT name, 
       CASE 
           WHEN department_id = 1 AND salary > 50000 THEN 'HR Manager'
           WHEN department_id = 2 AND salary > 40000 THEN 'Sales Lead'
           ELSE 'Staff'
       END AS job_role
FROM employees;

Explanation:

This query assigns specific job roles based on a combination of department and salary.

Output:

name	salary_info
Alice	60000
Bob	No Salary Info
Charlie	55000

The CASE statement in SQL is a versatile and powerful tool for introducing conditional logic into your queries. It can be used in various parts of a query, from SELECT to ORDER BY, to perform complex data transformations, aggregations, and filtering. With a deep understanding of both simple and searched CASE statements, as well as advanced use cases like nested CASE and handling NULL values, you can significantly enhance the flexibility and functionality of your SQL queries. Happy coding !❤️

Conditional Logic with SQL CASE

Basic Syntax

Simple CASE Syntax:

Searched CASE Syntax:

Simple CASE vs Searched CASE

Simple CASE:

Example:

Explanation:

Output:

Searched CASE:

Example:

Explanation:

Output:

Using CASE in SELECT

Example:

Explanation:

Output:

CASE with Aggregate Functions

Example:

Explanation:

Output:

Using CASE in ORDER BY

Example:

Explanation:

Output:

CASE in WHERE Clauses

Example:

Explanation:

Output:

Nested CASE Statements

Example:

Explanation:

Output:

Handling NULL Values with CASE

Example:

Explanation:

Output:

Advanced Use Cases

CASE for Data Transformation:

Example:

Explanation:

Output:

Table of Contents

Explore

Popular Tutorials

Contact here

Simple `CASE` Syntax:

Searched `CASE` Syntax:

Simple `CASE`:

Searched `CASE`:

`CASE` for Data Transformation: