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:

CASE 
	WHEN value_1 THEN result_1 
	WHEN value_2 THEN result_2 
	… 
	ELSE default_result 
END

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:

SELECT  
  CASE 
	WHEN score > 80 THEN 'Large'
	WHEN score > 60 THEN 'Medium' 
	ELSE 'Small' 
  END AS result 
FROM your_table;

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:

SELECT  
  CASE day_of_week
	WHEN 'Saturday' THEN 'Weekend'
	WHEN 'Sunday' THEN 'Day of rest'
	ELSE 'Work day' 
  END AS day_type 
FROM your_table;