java在where in子句中没有值时如何执行查询

Question

我有一个根据不同id获取结果的查询， 我的查询如下所示： 我已经缩短了查询，但是，还有两个WHERE In条件

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

当第二个WHERE in条件中没有值时，如何执行此查询？ 或者如何在应用程序级（Java）中处理这个问题，我需要编写大约28个if-else条件来检查空条件。如何克服这个问题

Answer 1

这是一个应用程序任务，不是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有点混乱，但仍然不错
• 处理日期范围和其他变化也是可能的

Answer 2

我通常通过增加布尔变量来解决这种情况。 在您的示例中，我有一个额外的布尔参数，名为SearchByCollectionId，因此它类似于：

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

由于支架中的第一个条件不满足，因此不会检查第二个条件。所以你会得到一切

Answer 3

如果不将“餐厅美食”和“餐厅收藏”与其他项目联系起来，SQL就没有真正的意义。如果这两个表与其他表不相关，那么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 ();

例如，我们有两列（C1和C2）的表A和表B

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
  ...

首先，将表与所需字段关联起来。然后使用适当的联接（左联接或外联接）建立关系。如果进行了更正，则与联接的第二部分不相关的行将为空（如空集合），并且可以选择其值为空的所有行（ej:restaurant_collections.collection_id为空）

示例：（需要填写缺失的关系）

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

Answer 4

如果您只想让查询工作，那么您应该能够使用：

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子句中引用它时，如果通过使用COALESCE函数传递的值不是空的，则可以轻松检查C.v是否包含值列表

1. 场景1中有值，只返回这些值
2. 场景2，and=NULL中没有值。归还一切

总之，LEFT连接非常灵活，允许您稍后返回并选择性地强制执行严格的INNER JOIN。它们不应该在INNER JOIN执行任务时使用，但它们肯定有自己的用途，比如这里

---

关于LEFT与INNER的注释：

• 如果希望在列表为空的情况下执行查询，请使用LEFT JOIN
• 如果希望查询仅在列表中有值时返回结果，请使用INNER JOIN

Answer 5

这个问题似乎缺乏一些解释。对于其他人来说，帮助你的最佳方式是对以下方面进行深入了解：

1. 您的实体关系（ER）模型。我看到了四张表格，但不清楚表格之间的关系
2. ER代表什么以及你想要实现什么，简单的语言也能有所帮助

我可能会在你的提问中补充一些一般性意见：

• 为了避免“笛卡尔积”，需要建立N-1 WHERE子句来连接表。有关笛卡尔积的快速信息，请访问：Cartesian product。我假设您希望避免这种情况，但您仅用一个子句连接了两个表，然后继续缩小结果范围，因此您缺少了另外两个WHERE子句。这是你的意图还是疏忽
• 您使用DISTINCT关键字来避免重复，大概是为了处理笛卡尔（副）积。在你的案例中，它不如使用分组方法。这可能会让你很好地理解为什么：https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct
• 您正在使用隐式联接。我建议您开始使用显式连接，例如左连接、内部连接等。这使您的查询更易于阅读和维护。在其他议题上也提到了这一点，即：implicit vs explicit sql join
• 倾向于IN（空）。与其他建议或暗示您在（null）中使用的答案不同，我建议您使用x为null的地方。更多信息请访问 IN clause with NULL or IS NULL

Answer 6

这完全取决于你的需要。通常你应该跳过整个部分

当您对所有条件使用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)