The SQL CASE Expression

The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

Example

 CASE 

  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
 WHEN conditionN THEN resultN 
 ELSE result

 END ;

Demo Products table

This product table is used in examples:

SQL CASE Examples

The following SQL goes through conditions and returns a value when the first condition is met:

Example

  SELECT  Product_id,Quantity,
 CASE 
 WHEN Quantity > 20  THEN  'The quantity is greater then 20'   
 WHEN Quantity = 20  THEN 'The quantity is 20'
              
 ELSE 'The quantity is under 20'
 END AS  QuantityText
 FROM  products ;

You can click on above box to edit the code and run again.

Output

The following SQL will order the employee by City. However, if City is NULL, then order by Country:

Example

 SELECT  Employee_name,City,Country
 FROM  employee
 ORDER BY (
 CASE 
  WHEN City IS NULL  THEN  Country   
              
              
 ELSE City
 END); 
]

You can click on above box to edit the code and run again.