Simply SQL - Where Clause Performance

[NotionCommotion]

I have a question regarding Simple SQL - Where Clause Performance Page 99.

There are four ways to perform the same query: NOT EXISTS, NOT IN (uncorrelated), NO IN (correlated), and LEFT OUTER JOIN with an IS NULL test.

Assuming keys are indexed, the fasted query is the one which does not need to retrieve the cart rows. This is the LEFT OUTER JOIN example, correct?

Thanks

[r937]

not quite

none of the 4 example queries should need to access the carts table at all, assuming the index on carts.customer_id exists

as to which one is fastest, i mention on page 100 that they would likely all perform equally well, and direct you to Appendix A

however, in the real world, with real tables, your results may vary, and the advice i give in Appendix A (page 257) is to examine the query execution plans to see whether or how they differ


p.s. welcome to sitepoint forums

[NotionCommotion]

Thank you for your reply and your welcome!