SQL WHERE ID IN (id1, id2, …, idn)

Question

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

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

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

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

  }

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 the correct solution to this problem?

Answer 1

The first approach is best because this reduces the SQL Server response time.  The second approach does the same but you are repeating the same column multiple columns and hence not efficient compared to the first approach. The third approach is not efficient compared to the other two approaches in performance-wise because it needs to run a query for each iteration and the database will be hammered with several small queries.

To get expertise in writing optimized SQL queries, I recommend this SQL Training course by Intellipaat