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 in Database by Kithuzzz
• 11,640 points
38 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 by narikkadan
• 19,680 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
583 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

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

answered Feb 7 in Database by Vaani
• 7,020 points
115 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 in Database by Vaani
• 7,020 points
88 views
0 votes
0 answers

SQL use CASE statement in WHERE IN clause

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

Aug 9 in Database by Kithuzzz
• 11,640 points
33 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 in Database by Vaani
• 7,020 points
119 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 in Database by Vaani
• 7,020 points
44 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 in Database by Neha
• 8,920 points
2,030 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 in Others by anisha
• 140 points
32 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 in Database by narikkadan
• 19,680 points
32 views
0 votes
1 answer

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

Solution Go to Start > Programs > Microsoft SQL Server > Enterprise Manager. Right-click the SQL ...READ MORE

answered Sep 12 in Database by narikkadan
• 19,680 points
34 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP