MySQL MySQL CASE In WHERE CLAUSE: Conditional conditions

MySQL CASE In WHERE CLAUSE: Conditional conditions

Using MySQL CASE In WHERE CLAUSE: Conditional conditions in MySQL -TechBriefers

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.
Categories data
Create products table and insert data in it.
Products data

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.

Left Join result

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.

MySQL CASE In WHERE CLAUSE: Conditional conditions - Left Join with where

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.

MySQL CASE In WHERE CLAUSE: Conditional conditions - final result

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.

Leave a Reply