CASE Function - SQL

Overview

The CASE function in SQL is used to perform conditional logic within a query. It allows you to evaluate conditions and return a value when the first condition is met (similar to if-else statements in other programming languages).

Example:

SELECT
    CASE
        WHEN vacation_days <= 0 THEN "No vacation left"
        WHEN vacation_days >= 0 THEN "Some vacation left"
        ELSE "Missing Data"
    END AS vacation_overview
FROM company.employees

Syntax:

SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result_default
    END AS alias_name
FROM table

WHEN condition THEN result: This specifies a condition to evaluate and the result to return if the condition is true.

ELSE result_default: This specifies the result to return if none of the preceding conditions are true.

END AS alias_name: This assigns an alias (optional) to the column generated by the CASE expression.

Sample Data:

    
            first_name
            vacation_days
        
            Frank
            5
        
            Jane
            2
        
            Ashley
            3
        
            Glenn
            -3
        
            Kelly
            2
        
            Richard
            -7
        
            George
            2
        
            Kyle
            1
        
            James
            -2
        
            Gustavo
            -10

first_name	vacation_days
Frank	5
Jane	2
Ashley	3
Glenn	-3
Kelly	2
Richard	-7
George	2
Kyle	1
James	-2
Gustavo	-10

Example: Query without the WHERE Statement

In this example, the CASE Statement is categorizing employees’ vacation status based on the vacation_days column. It outputs as the vacation_overview column with values like “No vacation left”, “Some vacation left”, or “Missing Data depending on the vacation_days values in the company.employees table.

Example: Query with the WHERE Statement

In this example, we are filtering for only rows in department_id 1. The CASE Statement is categorizing employees’ vacation status based on the vacation_days column. It outputs as the vacation_overview column with values like “No vacation left”, “Some vacation left”, or “Missing Data depending on the vacation_days values in the company.employees table.