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.


Options: ReplyQuote

Written By
Optimizing SQL IN Clause
November 23, 2021 12:46PM
November 23, 2021 01:31PM

Sorry, you can't reply to this topic. It has been closed.

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.