SQL WHERE ID IN id1 id2 idn

0 votes

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?

Sep 11, 2022 in Database by Kithuzzz
• 38,010 points
4,974 views

1 answer to this question.

0 votes

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"

answered Sep 12, 2022 by narikkadan
• 63,420 points

Related Questions In Database

0 votes
1 answer

IN vs OR in the SQL WHERE Clause

I assume you want to know the ...READ MORE

answered Sep 24, 2018 in Database by DataKing99
• 8,240 points
3,299 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

Without a case statement: SELECT column1, ...READ MORE

answered Feb 7, 2022 in Database by Vaani
• 7,020 points
1,073 views
0 votes
1 answer

What is the difference between HAVING and WHERE in SQL?

HAVING: It is used to check after the aggregation ...READ MORE

answered Feb 17, 2022 in Database by Vaani
• 7,020 points
566 views
0 votes
0 answers

SQL use CASE statement in WHERE IN clause

Can you use a case statement in ...READ MORE

Aug 9, 2022 in Database by Kithuzzz
• 38,010 points
419 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,020 points
576 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4, 2022 in Database by Vaani
• 7,020 points
317 views
0 votes
1 answer

SQL SELECT WHERE field contains words

Use this query to include any of words: SELECT * ...READ MORE

answered Feb 21, 2022 in Database by Neha
• 9,060 points
18,835 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL server?

In SQL Server, it is possible to insert ...READ MORE

answered May 30, 2022 in Others by anisha
• 140 points
357 views
0 votes
1 answer

How to delete multiple rows in SQL where id = (x to y)

If you need to delete based on ...READ MORE

answered Sep 17, 2022 in Database by narikkadan
• 63,420 points
4,749 views
0 votes
1 answer

How do I run an sql query in php?

Try this: <?php $conn = new mysqli('localhost', 'jaydeep_mor', 'jaydeep_mor', ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,420 points
482 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP