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
Introduction to Db2
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
The following shows the syntax of the simple
CASE expression WHEN expression_1 THEN result_1 WHEN expression_2 THEN result_2 ... WHEN expression_n THEN result_n [ ELSE else_result ] END
In this syntax, Db2 compares the
expression in the
CASE clause with each expression (
expression_2, …) in the
WHEN clause sequentially from top to bottom.
Db2 returns the corresponding result in the
THEN clause (
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.
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;
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
- If a book has two authors, the
- If a book has more than 2 authors, the
'More Than Two Authors'specified in the
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
In this syntax:
expression_2,… are Boolean expressions.
result_2, … are possible results.
CASE expression evaluates
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
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;
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.