Page 27 - SQL

Chapter 5: CASE

Introduction

The CASE expression is used to implement if-then logic.

Syntax

• CASE input_expression
WHEN compare1 THEN result1
[WHEN compare2 THEN result2]...
[ELSE resultX]
END
• CASE
WHEN condition1 THEN result1
[WHEN condition2 THEN result2]...
[ELSE resultX]
END

Remarks

The simple CASE expression returns the first result whose compareX value is equal to the
input_expression.

The searched CASE expression returns the first result whose conditionX is true.

Examples

Searched CASE in SELECT (Matches a boolean expression)

The searched CASE returns results when a boolean expression is TRUE.

(This differs from the simple case, which can only check for equivalency with an input.)

SELECT Id, ItemId, Price,
CASE WHEN Price < 10 THEN 'CHEAP'
WHEN Price < 20 THEN 'AFFORDABLE'
ELSE 'EXPENSIVE'
END AS PriceRating
FROM ItemSales

Id ItemId Price PriceRating

1 100 34.5 EXPENSIVE

2 145 2.3 CHEAP

https://riptutorial.com/ 9