MySQL Forums
Forum List  »  Performance

Optimizing SQL IN Clause
Posted by: Janez Makovsek
Date: November 23, 2021 12:46PM

Dear All,

When we have:
SELECT * FROM table1 WHERE (table1.clientid = 10) AND (table1.partnerid NOT IN (17,...,210))
SELECT * FROM table1 WHERE (table1.clientid = 11) AND (table1.partnerid NOT IN (211,...,500))
...
SELECT * FROM table1 WHERE (table1.clientid = 1100) AND (table1.partnerid NOT IN (22000,...,22500))

Would it be possible to write a single query, for many (10 000) clientid's, if all partnerid's are unique for all clientid's?

A simple query that works:

SELECT * FROM table1 WHERE (table1.clientid IN (10,...,1100)) AND (table1.partnerid NOT IN (17,...,22500))

will be checking for presence of partnerid's also for those clientid's, where the partnerid's dont exist anyway. This would inevitably and unnecessarily lead to O(N2) cost of comparisons.

Thanks!
Atmapuri

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing SQL IN Clause
293
November 23, 2021 12:46PM
148
November 23, 2021 01:31PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.