SQL Case

Consider the SQL CASE statement an if-then logic.

The basic syntax for a SQL CASE statement is as follows:

SELECT column_name,
 CASE
  WHEN condition THEN 'Result_1'
  WHEN condition THEN 'Result_2'
  ELSE 'Result_3'
 END
FROM table_name;

In this syntax, the keyword CASE is followed by a series of WHEN clauses containing a condition and a result. If the condition is met, the result is returned. If none of the conditions are met, the ELSE result is returned.

Here is an example of a SQL CASE statement:

SELECT book_title,
 CASE
  WHEN price > 100 THEN 'Expensive'
  ELSE 'Affordable'
 END as price_category
FROM bookshop;

In this example, the CASE statement checks the price for each book. If the price exceeds 100, the result is ‘Expensive’. If the price is less than or equal to 100, the result is ‘Affordable’. The result is returned as a new column called price_category.

If a column will be tested for equality against simple literal values, a simpler form is also available:

SELECT column_a,
 CASE column_b
  WHEN 'FINISHED' THEN 'OK'
  WHEN 'READY' THEN 'OK'
  ELSE 'FAIL'
 END
FROM table_name;

The CASE statement in the SQL WHERE clause filters the results of a query. Here is an example:

SELECT title, price 
FROM bookshop 
 WHERE 
  CASE
    WHEN genre = 'Romance' AND price < 20 THEN 1
    WHEN genre = 'Classics' AND price < 15 THEN 1
    ELSE 0
  END = 1;

In this example, the CASE statement checks the value of the genre and price columns for each product. If the genre is ‘Romance’ and the price is less than 20, or if the genre is ‘Classics’ and the price is less than 15, the result is 1. If neither condition is met, the result is 0.

The WHERE clause then filters the results only to include books where the result of the CASE statement is 1.

CASE statements may also be used in ORDER BY clauses for conditional or complex sorting.