Как выполнить запрос, когда в предложении where in нет значений

Условие IN () вызовет исключение синтаксиса в MySQL.

Вы можете удалить предложение AND, если список обнаружен как пустой. Проанализируйте запрос и, если обнаружен (), удалите условие AND.

Другой подход - всегда иметь там условие, которое не будет соответствовать никаким критериям в данных. Например, AND restaurant_collections.collection_id IN (-1), а затем продолжайте добавлять в список. Например, AND restaurant_collections.collection_id IN (-1,10,12). Таким образом, никогда не будет ситуации с пустым списком.

Все зависит от ваших потребностей. Обычно вы должны просто пропустить всю часть.

Поскольку вы используете AND для всех условий, я предполагаю, что по пустому списку вы хотите вернуть все строки (я предполагаю, что у конечных пользователей есть несколько флажков или что-то в этом роде).

Если столбец определен как NOT NULL, вы можете сгенерировать свой код в JAVA следующим образом:

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

Если вам нужны определенные значения, вы используете его, как и в предыдущем случае:

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

Это задача приложения, а не задача SQL.

Когда у меня есть API с несколькими дополнительными входами, я создаю WHERE динамически. Что-то вроде (извините, это на PHP, а не на 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
               ...";

Заметки:

• Если запись для x отсутствует, то эта часть WHERE опускается.
• Если ни один из параметров не указан, я даже полностью избавляюсь от предложения WHERE.
• Обращение с IN немного сложнее, но все же неплохо.
• Также возможны обработка диапазона дат и другие варианты.

Обычно я решаю такие условия с помощью дополнительной логической переменной. В вашем примере у меня был бы дополнительный логический параметр с именем searchByCollectionIds, чтобы он хотел что-то вроде:

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

Когда: collectionIds пуст, вы должны ввести в него -1, для: searchByCollectionIds установить значение false, и ваш запрос будет преобразован в:

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

Поскольку первое условие в скобке не выполняется, второе условие НЕ проверяется. Так ты получишь все.

Если все, что вам нужно, это чтобы запрос работал, вы сможете использовать:

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

Но это всегда будет безрезультатно.

Если вам нужно что-то более гибкое или если вы ожидаете всех результатов, если collection_id не имеет переданных ему значений, я рекомендую перейти с неявного на явное объединение.

Прямо сейчас вы используете синтаксис неявного соединения:

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

Проблема в том, что если C.j пуст, даже если вы исправите свой синтаксис, ваш запрос ничего не вернет.

Явные соединения позволяют использовать более тонкий подход:

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

При кодировании вместо передачи пустой строки передайте нулевое значение (или, если вы не используете подготовленные операторы, передайте строку, содержащую NULL). В противном случае передайте значения в виде списка.

Если вам нужно, чтобы строки игнорировались только тогда, когда нет значений, вы можете вместо этого сделать что-то вроде этого:

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

Когда вы добавляете LEFT JOIN и затем ссылаетесь на него в предложении WHERE, вы можете легко проверить, содержит ли C.v список значений, если переданное значение не является нулевым, с помощью функции КОЛЕСЦЕ.

1. Сценарий 1, в нем есть значения, возвращаются только эти значения.
2. Сценарий 2, нет значений в и = NULL. Верните все.

Таким образом, соединения LEFT очень гибкие и позволяют вернуться позже и, при необходимости, обеспечить строгий режим INNER JOIN. Их не следует использовать, когда INNER JOIN выполняет эту работу, но они определенно найдут свое применение, например, здесь.

Замечание о LEFT и INNER:

• Если вы хотите, чтобы запрос выполнялся, даже если список пуст, используйте LEFT JOIN.
• Если вы хотите, чтобы запрос возвращал результаты, только если в списке есть значения находятся, используйте INNER JOIN.

На самом деле SQL не имеет смысла, если вы не связываете "restaurant_cuisines" и "restaurant_collections" с другими элементами. Если эти две таблицы не связаны с другими, тогда SQL является декартовым произведением этих таблиц.

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

Например y у нас есть таблицы A и B с двумя столбцами (C1 и C2)

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

Создание SQL

SELECT * FROM C1, C2

выбирает все комбинации между A и B, сохраняя "дыры" с нулевым значением.

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

Сначала свяжите таблицы с нужными полями. Затем сделайте отношения, используя правильное СОЕДИНЕНИЕ (ЛЕВОЕ СОЕДИНЕНИЕ или ВНЕШНЕЕ СОЕДИНЕНИЕ), если вы сделаете это правильно, строки без отношения со второй частью СОЕДИНЕНИЯ будут нулевыми (например, пустая коллекция), и вы можете выбрать все строки с нулевым значением на нем (например, restaurant_collections.collection_id IS NULL)

Пример: (Требуется заполнить недостающие отношения)

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

Предполагая, что у вас есть переменная между этими скобками, вы можете проделать этот небольшой трюк.

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

По сути, он проверяет, является ли @VARIABLENULL, и не включает параметр.

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

Измените свой запрос на ДИНАМИЧЕСКИЙ ЗАПРОС, например,

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

Вышеупомянутое всегда будет выполняться со всеми значениями (в основном говоря, что это правда), теперь на основе ваших условий if / else вам просто нужно добавить ваше «условие И» не пустое, как это

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)");
}

Таким образом, приведенное выше будет добавлено только в том случае, если kitchen_id не равен нулю. Это заставляет ваш запрос выполняться только на основе требуемых условий и не добавлять ненужные дополнительные условия, которые могут изменить ваш результат или столкнуться с проблемами, с которыми вы в настоящее время страдаете.

Надеюсь это поможет :)

Я разработчик SQL Server, поэтому сделал бы это так:

/*
@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

Затем просто вызовите этот SP из своего Java-кода

Вы можете попробовать это:

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

Прежде всего, в следующий раз попытайтесь скопировать весь код, генерирующий этот запрос, кстати, как вы сказали, мне кажется, что вы получаете это из нескольких полей ввода и объединяете их в предложение IN

Вы должны создать набор для хранения этих полей перед накачкой, а затем в IN

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

collectionIds.remove(null);

тогда вы можете просто проверить, является ли набор идентификаторов пустым, прежде чем создавать запрос и обрабатывать его так, как вы хотите (просто игнорируйте запрос или создайте его без этого оператора)

Кстати, вам следует подумать об использовании Hibernate или другой среды сохранения, категорически не рекомендуется строить запросы на java или любом другом языке путем объединения строк. Это приводит к внедрению sql

Если ваша база данных поддерживает это, вы можете использовать ЛЮБОЙ (как в следующем примере с 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()]))

ЛЮБОЙ работает также с пустым массивом (по крайней мере, в postgres).

Вы идете в правильном направлении, напишите if / else, чтобы удалить условие пустого массива.

Но вам не нужно писать так много (~ 28 функций), просто абстрагируйте их до одной функции.

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, ",") + ") ");
  }
}

Надеюсь, вы уловили идею.

То, как я хотел для себя этого сценария, похоже на @PaulSpiegel. Хотя я не буду сначала проверять весь пустой массив или даже вводить запрос, для небольшого coct это все равно увеличивает время. Я бы добавил StringBuilder, и если массив не пуст sql.append( AND restaurant_cuisines.cuisine_id IN (?,?) ), для параметра действуют те же условия.

Кажется, этому вопросу не хватает объяснений. Лучший способ помочь другим - дать хорошее представление о:

1. Ваша модель сущности-отношения (ER). Я вижу четыре задействованных таблицы, но неясно отношения между таблицами.
2. Что представляет собой ER и чего вы хотите достичь, простые слова тоже могут помочь.

Вот несколько общих замечаний, которые я мог бы добавить к вашему запросу с самого начала:

• Чтобы избежать "декартова произведения", вам необходимо установить предложения WHERE N-1 для соединения ваших таблиц. Краткая информация о декартовом произведении доступна здесь: Декартово произведение. Я предполагаю, что вы хотите избежать этого, но вы соединили только две таблицы с одним предложением, а затем продолжили сужение результатов, поэтому вам не хватает двух других предложений WHERE. Было ли это вашим намерением или упущением?
• Вы используете ключевое слово DISTINCT, чтобы избежать дублирования, предположительно для работы с декартовым (побочным) произведением. Это менее эффективно, чем использование подхода GROUP BY в вашем случае. Это может дать вам хорошее представление, почему: https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumings-group-by-distinct
• Вы используете неявные объединения. Я предлагаю вам вместо этого начать использовать ЯВНЫЕ соединения, то есть LEFT JOIN, INNER JOIN и т. д. Это упрощает чтение и обслуживание вашего запроса. Это было упомянуто в других темах, например: неявное и явное соединение sql
• Склонность к IN (ноль). В отличие от других ответов, которые предлагают или продолжают намекать на использование IN (null), я советую вам вместо этого использовать WHERE x IS NULL. Подробнее на Предложение IN с NULL или IS NULL