case

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.

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

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

SELECT
COUNT(Id) AS ItemsCount,
SUM ( CASE
WHEN PriceRating = 'Expensive' THEN 1
ELSE 0
END
) AS ExpensiveItemsCount
FROM ItemSales

CASE in a clause ORDER BY

SELECT * FROM DEPT
ORDER BY
CASE DEPARTMENT
WHEN 'MARKETING' THEN 1
WHEN 'SALES' THEN 2
WHEN 'RESEARCH' THEN 3
WHEN 'INNOVATION' THEN 4
ELSE 5
END,
CITY

Using Case in UPATE

UPDATE ItemPrice
SET Price = Price *
CASE ItemId
WHEN 1 THEN 1.05
WHEN 2 THEN 1.10
WHEN 3 THEN 1.15
ELSE 1.00
END