SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simply SQL - Where Clause Performance

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your reply and your welcome!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •