How to Write a Case Statement
When you write a CASE statement in SQL, you define a specific condition or set of conditions to evaluate and specify the resulting output or action for each possible condition. The statement then evaluates the condition and executes the corresponding action based on the result.
The basic syntax for a CASE statement is as follows:
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
For example, if you want to return “Yes” if the column score is greater than 80 and “No” otherwise, you can use the following CASE statement:
WHEN score > 80 THEN 'Large'
WHEN score > 60 THEN 'Medium'
END AS result
In this example, the CASE statement checks if the value of the score column is greater than 80. If it is, it returns “Large”. Otherwise, it evaluates the next condition (score > 60) and if so, returns ‘Medium’. If none of the WHEN conditions are true, the ELSE value off “Small” is returned.
When all cases test for equality, an more compact CASE syntax can be used:
WHEN 'Saturday' THEN 'Weekend'
WHEN 'Sunday' THEN 'Day of rest'
ELSE 'Work day'
END AS day_type