Optimizing SQL IN Clause

[Janez Makovsek]

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