In SQL discourse, when the number of conditions is very large, what better way to replace where.in conditional clauses

Source: Internet
Author: User
In SQL statements, when the number of conditions is very large, what better way to replace the where...in conditional clauses?
When the Where condition in the SQL statement is where ID in (1,2,3,4,8,11,23,56,89,110,...), that is, when the number of in is quite large, the SQL statement is very poor, so what other better way to solve the problem?

------Solution--------------------
Separate the ID in the in to query, and then merge together.
------Solution--------------------
SELECT * from X where id=1 union ALL SELECT * from X where id=2 union ALL ...
------Solution--------------------
That's a good one.
------Solution--------------------
Can you tell me how the ID in your list came from?
------Solution--------------------
That's not a very poor SQL statement, since you allow the 10,000 ID value, then why is the inclusion of the 10,000 ID worth the IN clause is inferior?
------Solution--------------------
So do you think it would be wiser to put the incoming ID group into the table first and then correlate the query?
------Solution--------------------
Or is it more sensible to iterate through the incoming ID groups and query them one after the other in the loop?
------Solution--------------------
discuss

Or is it more sensible to iterate through the incoming ID groups and query them one after the other in the loop?

------Solution--------------------
If you are interested, you can read this blog.
http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/

However, as the blog at the end of the said, only in a large number of cases can see the obvious effect.


If your ID is just a few, dozens of ... I do not think it is necessary to consider this difference.

I think the method of separate query is not available, because MySQL internal processing is probably not worse than that ...




Explore
When the Where condition in the SQL statement is where ID in (1,2,3,4,8,11,23,56,89,110,...), that is, when the number of in is quite large, the SQL statement is very poor, so what other better way to solve the problem?

------Solution--------------------
Agree upstairs, only a few, dozens of (or even hundreds of) in the ID inefficient. Or else you have a good idea.
------Solution--------------------
discuss

Agree upstairs, only a few, dozens of (or even hundreds of) in the ID inefficient. Or else you have a good idea.

------Solution--------------------
discuss
Can you tell me how the ID in your list came from?

------Solution--------------------
Explore

Reference:
Can you tell me how the ID in your list came from?

I think that is the point of the problem. If possible, combine the SQL statement that obtained this set of IDs with the SQL statement to be implemented later to make the associated query.

If this set of IDs is not obtained with an SQL statement, then I am afraid to re-examine the business requirements and implementation of the scheme, really need to do a large group of "no reason" id for batch processing it?


———————————————————————————......

------Solution--------------------
discuss
Not tested, do you mean that federated queries are better than where in?

------Solution--------------------
Hey, don't look at the blog I posted it ... That is inserting temporary table and then join query ... The data is larger than in the case faster.

discuss

Reference:

Reference:
Can you tell me how the ID in your list came from?

I think that is the point of the problem. If possible, combine the SQL statement that obtained this set of IDs with the SQL statement to be implemented later to make the associated query.

If this set of IDs is not obtained with an SQL statement, then I am afraid to re-examine the business requirements and implementation of the scheme, really need to do a large group of "no reason" id for batch processing it?


——————————......
  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.