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?

Better not to put it there in the first place. You would do this in the application, not in the database. — Shadow, Apr 29 '18 at 17:48
yeah tried in the application need to write lot of if else conditions — Manju, Apr 30 '18 at 06:20
"need to write lot of if else conditions" - Doing it the right way it would be only one: `if (array is not empty) { add IN condition to query }`. You are asking the wrong question. — Paul Spiegel, May 02 '18 at 11:00

Lukasz Szozda · Answer 1 · 2018-05-01T16:33:37.607

5

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)

edited May 01 '18 at 16:33

answered May 01 '18 at 16:26

Lukasz Szozda

162,964
23
234
275

1

So you are telling to pass this restaurant_collections.collection_id Instead of passing empty? – Manju May 01 '18 at 16:55
@Manju It is worth a try. – Lukasz Szozda May 01 '18 at 17:05

smcjones · Accepted Answer · 2018-05-01T23:16:40.527

If all you want is for the query to work, you should be able to use:

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 (NULL);

But this will always return no results.

If you want something a little more flexible, or if you are expecting all results if collection_id does not have any values passed to it, I recommend changing from implicit to explicit joins.

Right now you're using an implicit join syntax:

SELECT A.*, B.*, C.* FROM A,B,C 
WHERE A.j=B.j AND A.j=C.j

The problem with this is that if C.j is empty, even if you fix your syntax, your query will return nothing.

Explicit joins allow for a more nuanced approach:

SELECT A.*, B.*, C.* FROM A
INNER JOIN B ON A.j=B.j AND B.v IN (1,2)
LEFT JOIN C ON A.j=C.j AND C.v IN (NULL);

When you code, instead of passing an empty string, pass a null value (or, if you're not using prepared statements, pass a string containing NULL). Otherwise, pass the values as a list.

If you need the rows to be ignored only when there are no values, you can do something like this instead:

SELECT A.*, B.*, C.* FROM A
INNER JOIN B ON A.j=B.j AND B.v IN (1,2)
LEFT JOIN C ON A.j=C.j
WHERE (COALESCE(<val>) IS NULL OR C.v IN (<val>));

When you add the LEFT JOIN and then refer to it in the WHERE clause, you can easily check if the C.v contains the list of values, if the value passed is not null by using the COALESCE function.

Scenario 1, there are values in , return only those values.
Scenario 2, there are no values in and =NULL. Return everything.

In summary, LEFT joins are very flexible and allow you to go back later and optionally enforce a strict INNER JOIN. They should not be used when an INNER JOIN does the job, but most definitely have their uses, such as here.

A note on LEFT vs INNER:

If you want the query to execute even if the list is empty, use LEFT JOIN.
If you want the query to return results only if there are values in the list, then use INNER JOIN.

score 3 · Answer 3 · answered May 08 '18 at 14:47

This question seem to be lacking some explanations. Your best way for others to help you is to give a good insight on:

Your entity-relationship (ER) model. I see four tables involved but it's not clear about relationships among the tables
What the ER represents and what you want to achieve, plain words can help as well.

There are some general remarks that I might add to your query from a get-go:

In order to avoid "Cartesian product", you need to establish N-1 WHERE clauses to connect your tables. Quick info on Cartesian product is available here: Cartesian product. I presume that you want to avoid this, but you connected only two tables with one clause, then proceeded with narrowing the results, so you are missing other two WHERE clauses. Was that your intention or an omission?
You are using DISTINCT keyword to avoid duplicates, presumably to deal with the Cartesian (by)product. It's less performant than using a GROUP BY approach in your case. This might give you a good insight why: https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct
You are using implicit joins. I suggest you to start using EXPLICIT joins instead, i.e. LEFT JOIN, INNER JOIN, etc. It makes your query easier to read and maintain. It has been mentioned on other topics, i.e.: implicit vs explicit sql join
Inclination towards IN(null). Unlike other answers that suggest or keep hinting for you to use IN(null), I advise you to use WHERE x IS NULL instead. Read more at IN clause with NULL or IS NULL

score 2 · Answer 4 · answered May 01 '18 at 17:18

I usually solve this kind of conditions by having additional boolean variable. In your example, I'd have an additional boolean param named searchByCollectionIds so that it would like something like:

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 (true = :searchByCollectionIds or restaurant_collections.collection_id IN (:collectionIds);

When :collectionIds is empty, you'd put -1 into it, :searchByCollectionIds set to false and your query would be translated into:

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 (true = false or restaurant_collections.collection_id IN (-1);

Since first condition in the bracket does not meet, it will NOT check second one. So you'll get everything.

score 1 · Answer 5 · answered May 01 '18 at 16:55

This is an app task, not an SQL task.

When I have an API with several optional inputs, I construct the WHERE dynamically. Something like (sorry, this is in PHP, not Java):

$wheres = array();    // declare empty array
if (user entered x)  $wheres[] = "x = '$x'; // conditionally append item to array
if (user entered y)  $wheres[] = "y = '$y';
if (user entered a non-empty zlist)
                     $wheres[] = "z IN (" . implode(',', $zlist), ")";

$where_clause = empty($wheres) ? '' :
                'WHERE ' . implode(' AND ', $wheres);

$SQL = "SELECT ...
           $where_clause
               ...";

Notes:

If there is no entry for x, then that part of the WHERE is omitted.
If none of the parameters are provided, I even get rid of the WHERE clause entirely.
Handling IN is a little messier, but still not bad.
Handling a date range, and other variations, are also possible.

score 1 · Answer 6 · answered May 02 '18 at 19:07

The SQL has no really sense if you don't relate "restaurant_cuisines" and "restaurant_collections" with the other items. If these two tables are not related with the other ones then the SQL is a cartesian product of this tables.

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 ();

For example y we have the tables A and B with two columns(C1 and C2)

Table values (A & B)
  C1  | C2
------|------
 1    |  1
 2    |  2

Making the SQL

SELECT * FROM C1, C2

selects all the combinations between A & B keeping the "holes" with a null value.

 A.C1 | A.C2 | B.C1 | B.C2
------|------|------|------
  1   |  1   |  1   | 1
  2   |  2   |  1   | 1
  1   |  1   |  2   | 2
  2   |  2   |  2   | 2
  ...

First, relate the tables with the desired fields. Then make the relations using a proper JOIN (LEFT JOIN or OUTER JOIN) if you make this correct the rows with out relation with the second part of the JOIN will be null (like an empty collection) and you could select all rows with null value on it (ej: restaurant_collections.collection_id IS NULL)

Example: ( Requires to fill the missing relations)

SELECT DISTINCT 
    restaurant.*,
    branch.* 
FROM
   restaurant r 
      LEFT JOIN branch b ON r.restaurant_id = b.restaurant_id
      LEFT JOIN restaurant_cuisines rcu ON  ????
      LEFT JOIN restaurant_collections rco ON ????
WHERE
   rcu.cuisine_id IN (2,3) AND
   rco IS NULL

score 1 · Answer 7 · answered May 04 '18 at 11:22

Assuming that you have a variable between those brackets you could do this little trick.

 DECLARE @Variable AS VARCHAR(250)


 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 (@VARIABLE) OR @VARIABLE IS NULL);

Essentially it tests to see if @VARIABLE is NULL and does not include the parameter.

score 1 · Answer 8 · answered May 07 '18 at 02:43

How to handle this in application level(Java), I need to write around 28 if else conditions to check empty conditions.

Change your query to a DYNAMIC QUERY like so

StringBuffer sql = "SELECT DISTINCT restaurant.*,branch.*"+
 "from restaurant,branch,restaurant_cuisines,restaurant_collections"+
" WHERE  1=1 ";

The above will always execute with all the values(basically saying its true), now based on your if/else conditions you just have to append your "AND condition" is not empty like so

How to execute this query when there are no values in second WHERE in Condition?

if(restaurant_cuisines.cuisine_id != null) {
sql.append("AND restaurant_cuisines.cuisine_id IN (2,3)");
}

So the above will only be added if cuisine_id is not null. What this does is makes your query run only based on the conditions required and not add unnecessary additional conditions which might alter your result or face the issues that you are current suffering with.

Hope this helps :)

score 1 · Answer 9 · answered May 07 '18 at 22:00

I'm a SQL Server developer so I would do it like this:

/*
@collection_id have to be a string with all the id's separated by comma like '1,2,3,4'
*/

CREATE PROCEDURE [dbo].[getRestaurant]
                                         @collection_id VARCHAR(MAX)
AS

DECLARE @query nVARHCAR(MAX)=''

SET @query= '
SELECT DISTINCT restaurant.*,branch.*  
FROM restaurant 
JOIN branch
ON restaurant.restaurant_id=branch.restaurant_id
JOIN restaurant_cuisines
ON  restaurant.restaurant_id = restaurant_cuisines.restaurant_id
JOIN restaurant_collections 
WHERE  restaurant_cuisines.cuisine_id IN (2,3) 
AND restaurant_collections.collection_id IN ('+@collection_id+')'

EXEC sp_executesql @query

Then just call this SP from your Java Code

score 1 · Answer 10 · answered May 08 '18 at 09:04

You can try this:

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 (true);

score 0 · Answer 11 · answered Apr 29 '18 at 10:47

0

The condition IN () will throw syntax exception in MySQL.

You can remove the AND clause if the list is detected as empty. Parse the query and if () is detected, strip out the AND condition.

Another approach is to always have a condition there which will not meet any criteria in the data. For example, AND restaurant_collections.collection_id IN (-1) and then keep appending to the list. For example, AND restaurant_collections.collection_id IN (-1,10,12). This way, there will never be an empty list situation.

answered Apr 29 '18 at 10:47

Jagrut Sharma

4,574
3
14
19

`AND restaurant_collections.collection_id IN (-1)` this won't work (return 0 rows) if by empty list OP wants all records (common scenario) – Lukasz Szozda May 01 '18 at 16:22
@lad2025 is right and also, having a false AND condition will always return 0 rows if the user leaves empty one of the n parameters. Consider for example: `AND restaurant_cuisines.cuisine_id IN (-1,2,3) AND restaurant_collections.collection_id IN (-1)` returns 0 rows. – LDMdev May 08 '18 at 12:41

score 0 · Answer 12 · answered May 08 '18 at 11:58

First of all next time try to copy the whole code generating that query, by the way you said it seams to me you are getting that from several input fields and merging then into the IN clause

You should create a Set to store these fields before inflate then into the IN

Set<String> collectionIds = new Hashset<>();
collectionIds.add(field1);
collectionIds.add(field2);
....

collectionIds.remove(null);

then you can just check if the id set is empty before create the query and handle it the way you want (simple ignore the query, or build it without this statement)

By the way you should consider using Hibernate or other persistence framework, it is strongly not adivsed to build queries in java or any other language by concating strings. It leads to sql injection

score 0 · Answer 13 · answered May 08 '18 at 13:03

If your database support it, you could use ANY (as in following example with jdbi3)

String sql = "SELECT * FROM table where collection_id = ANY (:ids);" List<Long>ids = ...; org.jdbi.v3.core.Handle db = getHandle(); db.createQuery(sql).bind("ids", ids.toArray(new Long[ids.size()]))

ANY works also with empty array (at least in postgres).

score 0 · Answer 14 · answered May 08 '18 at 13:15

You come with the right direction, write if/else to strip the empty array condition.

But you don't need to write that much (~28 functions), just abstract them to a single function.

StringBuilder baseQuery = new StringBuilder("SELECT DISTINCT restaurant.*,branch.* 
 from restaurant,branch,restaurant_cuisines,restaurant_collections 
 WHERE restaurant.restaurant_id=branch.restaurant_id ");

public void addInCondition(String field, Collection<Integer> values) {
  if (values != null && !values.isEmpty()) {
     // Append your values
     baseQuery.append(field + " IN (" + String.join(values, ",") + ") ");
  }
}

Hope you get the idea.

score 0 · Answer 15 · answered May 08 '18 at 14:42

The way i wanted to myself for this scenario is similar to @PaulSpiegel. Although i will not check all empty array or even put in in query in first.For small coct , it still increase the timing. I would append with StringBuilder and if array is not empty sql.append( AND restaurant_cuisines.cuisine_id IN (?,?) ), same condition apply for parameter.

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

15 Answers15