How to execute query when there are no values in where in clause

Question

I have a query that fetches results based on different id's, My query looks like this: I have shortened the query, still, there are two more WHERE In conditions.

 SELECT DISTINCT restaurant.*,branch.* 
 from restaurant,branch,restaurant_cuisines,restaurant_collections 
 WHERE restaurant.restaurant_id=branch.restaurant_id 
   AND restaurant_cuisines.cuisine_id IN (2,3) 
   AND restaurant_collections.collection_id IN ();

How to execute this query when there are no values in second WHERE in Condition? or How to handle this in application level(Java), I need to write around 28 if else conditions to check empty conditions. How to overcome this?

Answer 1

It all depends on your needs. Normally you should simply skip entire part.

As you use AND for all conditions I assume that by empty list you want to return all rows.(I guess that end users have some sort of multiple checkboxes or something like that).

If column is defined as NOT NULL you could generate your code in JAVA like this:

...
AND restaurant_collections.collection_id IN(restaurant_collections.collection_id)
-- which is always true and good query optimizer should skip entire part

When you want specific values you use it as previous:

...
AND restaurant_collections.collection_id IN (1,2,3)