SQL WHERE ID IN(ID1，ID2，...，IDN) [英] SQL WHERE ID IN (id1, id2, ..., idn)

问题描述

我需要编写一个查询来检索ID的大列表.

I need to write a query to retrieve a big list of ids.

我们确实支持许多后端(MySQL，Firebird，SQLServer，Oracle，PostgreSQL ...)，所以我需要编写标准的SQL.

We do support many backends (MySQL, Firebird, SQLServer, Oracle, PostgreSQL ...) so I need to write a standard SQL.

ID集的大小可能很大，查询将以编程方式生成.那么，最好的方法是什么?

The size of the id set could be big, the query would be generated programmatically. So, what is the best approach?

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

我的问题是.如果n很大，会发生什么?还有，性能如何?

My question here is. What happens if n is very big? Also, what about performance?

SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn

我认为这种方法没有n个限制，但是如果n非常大，性能会如何呢?

I think that this approach does not have n limit, but what about performance if n is very big?

  foreach (var id in myIdList)
  {
      var item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id);
      myObjectList.Add(item);
  }

当通过网络查询数据库服务器时，我们在使用这种方法时遇到了一些问题.通常，最好执行一个查询以检索所有结果，而不是进行许多小型查询.也许我错了.

We experienced some problems with this approach when the database server is queried over the network. Normally is better to do one query that retrieve all results versus making a lot of small queries. Maybe I'm wrong.

该问题的正确解决方案是什么?

What would be a correct solution for this problem?

推荐答案

选项1是唯一好的解决方案.

Option 1 is the only good solution.

• 选项2的作用相同，但是您重复列名称很多次；另外，SQL引擎不会立即知道您要检查该值是否为固定列表中的值之一.但是，良好的SQL引擎可以对其进行优化，使其具有与IN相同的性能.仍然存在可读性问题...

• Option 2 does the same but you repeat the column name lots of times; additionally the SQL engine doesn't immediately know that you want to check if the value is one of the values in a fixed list. However, a good SQL engine could optimize it to have equal performance like with IN. There's still the readability issue though...

选项3在性能方面简直太糟糕了.它在每个循环中发送一个查询，并通过少量查询来锤击数据库.它还会阻止它对值是给定列表中的值之一"使用任何优化.

Option 3 is simply horrible performance-wise. It sends a query every loop and hammers the database with small queries. It also prevents it from using any optimizations for "value is one of those in a given list"

这篇关于SQL WHERE ID IN(ID1，ID2，...，IDN)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！