SQL Server Performance

Which is better Not In or Not Exists

Discussion in 'General Developer Questions' started by nitingulati7, May 5, 2004.

  1. nitingulati7 New Member

    Wanted to know which of the following is better while joining the tables:
    NOT IN
    or
    NOT EXISTS
    and how can i modify my query from NOT IN to NOT EXISTS. will it give me the same results or different results.

    -NG

  2. Luis Martin Moderator

    Developer people I ask (I'm not developer) said Not In give more performance than Not exists.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

  3. Raulie New Member

    Can you post the query? I am no a developer but from what I know both plans are the same it is just a matter of preference either writing a simple Sub query (IN) or a Correlated Subquery (Exist), one thing is that with the Not Exists you dont have to write the column names as soon as one does not match it returns 1.
  4. tdong New Member

    I would go for Not Exist because MS SQL doesn't support INTERSECT and they create EXISTS to replace INTERSECT assumming performance is better. I am a programmer and I like EXISTS keyword since it is english like <img src='/community/emoticons/emotion-1.gif' alt=':)' /> hehheeh<br /><br />SELECT * FROM Table1<br />WHERE KeyField in<br />( SELECT Keyfield2<br /> FROM Table2, Table3<br /> WHERE Keyfield2 = Keyfield3<br />);<br /><br />This is not equivalent in the case of a NULL<br />Or maybe the following is more efficient <br /><br />SELECT * FROM Table1<br />WHERE EXISTS<br />( SELECT *<br /> FROM Table2<br /> WHERE KeyField2 = KeyField<br />)<br />AND EXISTS<br />( SELECT *<br /> FROM Table3<br /> WHERE KeyField3 = KeyField<br />);<br /><br /><br /><br />May the best cheaters win
  5. gaurav_bindlish New Member

    Yup! Not exists is generally faster than NOT IN

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  6. Luis Martin Moderator

    I'm goin to kill developers who I ask before.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

  7. tdong New Member

    Please don't kill them I am a developer too <img src='/community/emoticons/emotion-6.gif' alt=':(' /> <br />Performance is a relative term. Faster computer run a little faster but cost a fortune hehhe<br /><br /><br />You got to read about us. I love the quote so much that I actually printed it and framed it and put it on my front door and ask everyone to ready before asking question hehehhe<br /><br />Quoted from suresha_b<br /><br />"Theory is when you know something, but it doesn't work.<br />Practice is when something works, but you don't know why.<br /><br />Programmers combine theory and practice: "Nothing works and they don't know why"<br /><br /><br />May the best cheaters win
  8. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by LuisMartin</i><br /><br />I'm goin to kill developers who I ask before.<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Hey Luis, <br />if you want to be a little evil, you should wait and ask your developer whether some kind of JOIN or EXISTS is faster. When he answers JOINS are generally faster, go ahead and kill him.<br /><br />[}<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  9. Raulie New Member

    Yeah really sometimes I feel I should be the developer, when they ask me certain questions. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page