Sql Case Expression Syntax?

sql

conditional-aggregation

case-expressions

sql-queries

byAnton Shumikhin·Jan 17, 2025

The SQL CASE expression allows for conditional logic in your SQL queries, similar to an IF-ELSE statement. So, you can use this expression as:

SELECT 
    CASE WHEN condition THEN result 
    ELSE default END 
FROM table;

As an example, let's categorize age:

SELECT 
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age >= 18 THEN 'Adult'
    END AS AgeGroup
FROM people;

This query will categorize each age value as either 'Minor' or 'Adult' in the AgeGroup column.

Unlocking the CASE basics

When Simple meets Searched: The CASE of two types

SQL provides two types of CASE expressions: the simple CASE and the searched CASE, choice depends on your needs:

Simple CASE evaluates one expression against a set of specific values. Imagine asking a color-blind friend, "What color is the traffic light?" They may say:

SELECT 
    CASE color 
        WHEN 'red' THEN 'stop'   -- "Why are all these cars honking at me?"
        WHEN 'yellow' THEN 'caution' -- "Do I risk it or wait?"
        WHEN 'green' THEN 'go'  -- "Of course, it's always green when you're late!"
        ELSE 'unknown' 
    END AS traffic_signal
FROM signals;

Searched CASE is used with a set of Boolean conditions, like grading students:

SELECT 
    CASE 
        WHEN score >= 90 THEN 'Excellent'  -- "Did you find the cheat sheet or what?"
        WHEN score >= 80 THEN 'Good'  -- Typical overachiever
        WHEN score >= 70 THEN 'Average'  -- "C's get degrees!"
        ELSE 'Improvement needed'  -- "I can't believe I failed recess!"
    END AS grade
FROM exam_results;

Null, the ghost in the ELSE clause

Without an ELSE clause, the CASE expression will return NULL if none of the WHEN conditions are met. You can prevent these spooky apparitions by providing a default value with an ELSE clause:

CASE 
    WHEN condition THEN result 
    ELSE 'Default result'  --This ain't no ghost town!
END

Or, use COALESCE function to base-proof your query against these ghostly NULL values:

COALESCE(
    (CASE 
        WHEN condition THEN result 
    END), 
    'Default result' --The Ghostbusters are here!
)

Get the most out of your CASE expressions

Order: Sort your conditions by how likely they are to be true. Every second counts!
Complexity: Keeping conditions simple helps readability and maintenance – no one likes spaghetti code in their SQL, right?
Test CASEs: Make sure all possible scenarios in your IF-THEN-ELSE logic are handled.

CASE: Advanced Applications

Conditional aggregation in reporting

CASE really shines in conditional aggregations:

SELECT 
    SUM(CASE WHEN gender = 'Female' THEN amount ELSE 0 END) AS TotalFemaleSales,
    SUM(CASE WHEN gender = 'Male' THEN amount ELSE 0 END) AS TotalMaleSales
FROM sales;

Field-specific operations based on conditions?! That's like killing two birds with one stone!

Nesting CASE expressions

For more complicated logic, you might need to nest CASE statements:

SELECT 
    CASE 
        WHEN outer_condition THEN 
            (CASE
                WHEN inner_condition THEN 'Result A' -- Inception level 1
                ELSE 'Result B'  -- Inception level 2: CASE within CASE!
            END)
        ELSE 'Result C'
    END AS nested_result
FROM data;

It's almost like being in movie Inception, isn't it?

CASE expressions in JOINs

You can also use CASE in JOIN conditions to create dynamic joins:

SELECT *
FROM table1 
JOIN table2 ON 
    CASE 
        WHEN table1.column = 'condition' THEN table1.id = table2.matching_id
        ELSE table1.id = table2.default_id
    END