Post reply

Exists Vs IN

  • October 18, 2004 at 10:15 pm

    #60330

    Hi All,

    I want to know what exactly the difference between “EXISTS” and “IN” in sql query’s. I can write the same query with both of these. Performance point of view why EXISTS is better?

    Regards,

    Ramesh K

  • October 19, 2004 at 2:14 am

    #527130

    EXISTS is a boolean expression - IN is a list.

    EXISTS returns only true or false - no data from the expression in an EXISTS clause is returned. EXISTS also stops as soon as it's condition is found true.

    ie EXISTS - when the expression returns true, execution stops, while for an IN clause checking for the same value, even though one match has been found, IN keeps looking for more matches.

    EXISTS is just that - a check for existence. Either it's true or it's not. As soon as it's known, the expression is fulfilled. This behaviour may make an EXISTS construct more efficient than IN

    IN is a list, and it returns all rows that match the list - ie it's a short form of writing OR

    WHERE col1 IN (1, 2, 3) is transformed internally to WHERE (col1 = 1 OR col1 = 2 OR col1 = 3)

    There is no black or white rules where either technique applies, or is most beneficial - it depends.

    Also, BOL is an excellent place for more info on the subject.

    /Kenneth

     

  • October 19, 2004 at 12:14 pm

    #527230

    Above is a great explanation.

  • October 20, 2004 at 4:14 am

    #527290

    Gr8 explnations  Kenneth.

  • October 20, 2004 at 6:47 am

    #527306

    Kenneth.. Thanks for your great explnation.. now i am very clear about this..

    Thanks,

    Ramesh K 

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply