Fetch different columns conditionally using MySQL CASE
Before using MySQL CASE to fetch different columns based on condition, i.e. using where clause to select different column. Firstly, Lets take a look at the definition of CASE statement.
MySQL CASE is simply a sequence of one or more IF else conditions.
The CASE statement is set of conditions and return a value when a certain condition is true (like an IF-THEN-ELSE statement). So, once a condition is true, it stops reading further cases and return the result.
If none of the conditions are true, it will return the value of the ELSE clause.
If there is no ELSE clause and none of the conditions are true, it returns NULL.
CASE Statement Syntax:
|
1 |
CASE WHEN <condition1> THEN <execute for condition1 is true> WHEN <condotion2> THEN <execute for condition2 is true> ..... ELSE <default case> END |
Syntax Explanation:
CASE starts with CASE followed by n number of combinations of WHEN, THEN sequence as we do in if(){}else if(){}else{}…..else{}.
ELSE is placed in the last for making a default condition to run and is completely optional.
If any of the conditions given are not found true then ELSE case is executed if present, if else case is omitted then results NULL.
Table Used in further queries:
Please execute the following queries for creating table I have used in the following examples.
Query to create table.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `products` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `product` varchar(100) NOT NULL, `category` enum('Fruit','Beverage','Cereal') NOT NULL, `mrp` int(11) NOT NULL, `expiry` date NOT NULL, `in_stock` tinyint(4) NOT NULL DEFAULT '1' ); |
Query to insert data:
|
1 2 3 4 5 6 7 8 |
INSERT INTO `products` (`id`, `product`, `category`, `mrp`, `expiry`, `in_stock`) VALUES (1, 'Apple', 'Fruit', 100, '2019-05-09', 1), (2, 'Cheery', 'Fruit', 85, '2019-05-06', 1), (3, 'Mojito Fresh', 'Beverage', 120, '2019-05-04', 1), (4, 'Coke can', 'Beverage', 20, '2019-05-07', 1), (5, 'Rolled Oat', 'Cereal', 200, '2020-05-03', 1), (6, 'Corn ', 'Cereal', 30, '2020-05-03', 1), (7, 'Barley ', 'Cereal', 40, '2020-05-03', 1); |
The table we will be revering to looks like:
Examples of MySQL CASE:
1. Select different values based on column values
|
1 |
SELECT product, (CASE WHEN category = 'Beverage' THEN 'Is a beverage' WHEN category = 'Fruit' THEN 'Is a fruit' END) AS type FROM products |
Here i haven’t used ELSE case which results NULL in type when both cases are false.
Now Running same query with ELSE case
|
1 |
SELECT product, (CASE WHEN category = 'Beverage' THEN 'Is a beverage' WHEN category = 'Fruit' THEN 'Is a fruit' ELSE 'Not a beverage or Fruit' END) AS type FROM products |
2. Select columns based on condition
|
1 |
SELECT product, (CASE WHEN expiry > '2019-05-07' THEN CONCAT(product, ' is not expired') WHEN expiry = '2019-05-07' THEN CONCAT(product, ' is ',category) ELSE 'Expired' END) AS type FROM products |
Similarly any of the columns can be fetched based on condition and requirement. One column can be fetched in one case, however if you want to fetch more than one column then either you can use CASE statements multiple times or use concatenation functions as I demonstrated above.