MySQL CASE In WHERE CLAUSE: Conditional conditions

Before proceeding to know how to use MySQL CASE in where clause, be sure to be aware of what a MySQL CASE is. If not, click here to read.

Sometimes we have a requirement where we need to apply any clause depending on any condition. Whenever I face this requirement, most of the time my problem is resolved by using CASE statement in WHERE clause. Using CASE in where clause makes the query a bit complicate but if there is no way, it is the best option for me.

Let’s take a scenario where it can be demonstrated easily.
Suppose we are querying two or more table (currently taking only two) for some data using LEFT join or Right join. In case of Left or Right joins if data is present in left or right table then only data is returned, otherwise NULL is returned.

IF you are not familiar with Left and right joins, click here to read..

Here I am taking the example of Left join. In case if we need to apply a where clause on any column of the table on right, it will always consider the constraint and if constraint is not fulfilled values will not be fetched even there are values on the Left Side table.

Create categories table and insert data in it.

/* Create categories table */
CREATE TABLE `categories` (
  `cat_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
  `cat_name` varchar(100) NOT NULL
);

/* Insert categories data */
INSERT INTO `categories` (`cat_id`, `cat_name`) VALUES
(1, 'Fruit'),
(2, 'Beverage'),
(3, 'Cereal'),
(4, 'Cookware'),
(5, 'Furniture'),
(6, 'Garments');

/* Show categories Data */
SELECT * FROM `categories`;

Create products table and insert data in it.

/* Create products table */
CREATE TABLE `products` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `product` varchar(100) NOT NULL,
  `category` int(11) NOT NULL,
  `mrp` int(11) NOT NULL,
  `expiry` date NOT NULL,
  `in_stock` tinyint(4) NOT NULL DEFAULT '1'
);

/* Insert products data */
INSERT INTO `products` (`id`, `product`, `category`, `mrp`, `expiry`, `in_stock`) VALUES
(1, 'Apple', 1, 100, '2019-05-09', 1),
(2, 'Cheery', 1, 85, '2019-05-06', 1),
(3, 'Mojito Fresh', 2, 120, '2019-05-04', 1),
(4, 'Coke can', 2, 20, '2019-05-07', 1),
(5, 'Rolled Oat', 3, 200, '2020-05-03', 1),
(6, 'Corn ', 3, 30, '2020-05-03', 1),
(7, 'Barley ', 3, 40, '2020-05-03', 1);

/* Show products Data */
SELECT * FROM `products`;

Creating a simple join query from categories and products table, the data returned is of all categories and products if present in any category otherwise null.

SELECT * FROM `categories` LEFT JOIN `products` ON category = cat_id;

Now, if we need to fetch products(if present) having mrp greater than 80 and list all categories irrespective of presence of products in them. That is if there is no product return NULL, and if there are products list only those having mrp greater than 80. Hopefully the situation is clear.

Now let’s go through the query like we simply do then using case with it.

SELECT * FROM `categories` LEFT JOIN `products` ON category  = cat_id WHERE 1 AND mrp > 80;

Using MySQL CASE in Where Clause to fetch data by left Join.

Here we can see, if there is any product in a category with mrp greater than 80 then only category data is shown. and product details are NULL. Now using same clause with CASE.

SELECT * FROM `categories` LEFT JOIN `products` ON category = cat_id WHERE 1 AND (CASE WHEN mrp IS NOT NULL THEN mrp > 80 ELSE 1 END);

Now you can see in the results above that the returned list consist of products which have mrp that is satisfying the condition, While there is no effect on the categories listing which do not have any products. Henceforth category listing is irrespective of having products under it.