MySQL CASE FUNCTION

In MySQL, CASE function is used to implement conditional logic in queries. It operates similarly to an if-else or switch statement in programming. There are two forms of CASE in MySQL: Simple Case and Searched Case.

1) Simple Case

Syntax

Case expression
   When condition1 Then result1
   When condition2 Then result2
   ...
   ELSE default_result 
END 

Simple Case Example

This query retrieves information from a table named products and performs the following operations:

Example

SELECT product_id,
  CASE category_id
   WHEN 1 THEN 'Books'
   WHEN 2 THEN 'Mobiles'
   WHEN 3 THEN 'Laptops'
   ELSE 'Other'
 END AS category_name
   FROM products;

Try it Yourself

Demo Database:

Table Name : orders

order_no	order_date	customer_id	total_amount	status
2021001	2021-05-13	1	1545.00	Delivered
2021002	2021-05-13	2	18000.00	Delivered
2021003	2021-05-14	3	10000.00	Pending
2021004	2021-05-15	4	1450.00	Delivered
2021005	2021-05-16	5	4680.00	Pending
2021006	2021-05-17	6	10000.00	Pending
2021007	2021-05-18	7	5475.00	Delivered
2021008	2021-05-19	8	4337.00	Pending

2) Searched Case

Syntax

Case expression
   When condition1 Then result1
   When condition2 Then result2
   ...
   ELSE default_result 
END 

Searched Case Example

Retrieves the unique identifier of the order and Dynamically categorizes the price into an order priority from the order_details table.

Example

SELECT order_no,price,
  CASE
   WHEN price > 1000 THEN 'HIGH'
   WHEN price BETWEEN 500 AND 1000 THEN 'Medium'
   ELSE 'Low'
 END AS order_priority
   FROM order_details;

Try it Yourself