Db2 CASE Expression

Summary: in this tutorial, you will learn how to use the Db2 CASE expression to add if-else logic to the queries, including simple and searched CASE expressions.

Introduction to Db2 CASE expression

A CASE expression allows you to select an expression based on evaluation of one or more conditions. In other words, it allows you to add the if-else logic to your queries.

Db2 supports two kinds of CASE expressions: simple CASE and searched CASE expressions.

Both simple and searched CASE are expressions, therefore, you can use them in any clause that accepts an expression such as SELECT, WHERE, GROUP BY, and HAVING clauses.

Simple CASE expression

The following shows the syntax of the simple CASE expression:

CASE expression   
    WHEN expression_1 THEN result_1
    WHEN expression_2 THEN result_2
    ...
    WHEN expression_n THEN result_n
    [ ELSE else_result ]   
END  
Code language: SQL (Structured Query Language) (sql)

In this syntax, Db2 compares the expression in the CASE clause with each expression (expression_1, expression_2, …) in the WHEN clause sequentially from top to bottom.

Db2 returns the corresponding result in the THEN clause (result_1, result_2, …) if it finds a match (expression = expression1, expression = expression2…). Db2 immediately stops searching once it finds a match.

If Db2 does not find any match, it will return the else_result in case the ELSE clause is available. If you don’t specify the ELSE clause, the simple CASE expression will return NULL when it finds no match.

Simple CASE expression example

The following example returns the book title, the number of authors of each book, and a note:

SELECT 
    b.title, 
    COUNT(a.author_id) author_count,
    CASE COUNT(a.author_id)
        WHEN 1 THEN 'Single Author'
        WHEN 2 THEN 'Two Authors'
        ELSE 'More Than Two Authors'
    END note
FROM books b
 INNER JOIN book_authors a 
    ON a.book_id = b.book_id
GROUP BY b.title
ORDER BY b.title;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

In this example, we used the simple CASE expression to make the note columm with the following logic:

• If a book has one author, the CASE expression returns 'Single Author'.
• If a book has two authors, the CASE expression returns 'Two Authors'.
• If a book has more than 2 authors, the CASE expression returns 'More Than Two Authors' specified in the ELSE clause.

Searched CASE expression

The syntax of the searched CASE expression is the following:

CASE  
    WHEN expression_1 THEN result_1
    WHEN expression_2 THEN result_2
    ...
    WHEN expression_n THEN result_n
    [ ELSE else_result ]   
END 
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• expression_1, expression_2,… are Boolean expressions.
• result_1, result_2, … are possible results.

The searched CASE expression evaluates expression_1, expression_2… sequentially in each WHEN clause in the specified order until an expression evaluates to true. Then, the CASE expression returns the corresponding result and stops searching.

If no expression evaluates to true, the searched CASE expression returns the result in the ELSE clause or NULL if you don’t specify the ELSE clause.

Searched CASE expression example

The following example uses the searched CASE expression to return the comments based on the book ratings:

SELECT 
    title, 
    rating,
    CASE 
        WHEN (rating >= 1 AND rating < 2) THEN 'Not so good' 
        WHEN (rating >= 2 AND rating < 3) THEN 'Limited useful information'
        WHEN (rating >= 3 AND rating < 4) THEN 'Good book, but nothing special'
        WHEN (rating >= 4 AND rating < 5) THEN 'Incredbly special'
        WHEN rating = 5 THEN 'Life changing. Must Read.'
        ELSE
            'No rating yet'
    END AS comment
FROM 
    books
ORDER BY 
    title;
Code language: SQL (Structured Query Language) (sql)

Here is the partial result set:

In this tutorial, you have learned how to use the Db2 CASE expression to add if-else logic to the queries.