SQL WHERE ID IN id1 id2 idn

Question

I must create a query to get a lengthy list of IDs. I must create a standard SQL because we support a wide range of backends (MySQL, Firebird, SQLServer, Oracle, PostgreSQL, etc.).

The query would be generated programmatically, therefore the size of the id set could be substantial. So what is the best course of action?

1) Writing a query using IN

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

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

2) Writing a query using OR

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

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

3) Writing a programmatic solution:

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

When the database server is accessed across the network, we ran into several issues with this method. Typically, it is preferable to make a single, comprehensive query as opposed to numerous, narrower ones. Maybe I'm off base.

What would be the best response to this issue?

Answer 1

Option 1 is the only good solution.

Why?

• 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 an equal performance like with IN. There's still the readability issue though...

• 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"