SQL Server Performance Forum – Threads Archive

IN and EXISTS

If the sub query for an IN and EXISTS clauses are identical is there a reason to use an IN clause? Will the results ever be different? Also, most (if not all) examples for EXISTS use (select * from MyTable), I thought that using "*" was not a good idea. Is it still the case with an EXISTS clause? Thanks for any input. Dan Reber

No.
Actually you can use virtually any valid expression in the EXISTS clause. EXISTS performs a logical check whether at least one rows satisfies the condition and stops immediately once one row has been found and returns TRUE. No other data from the SELECT is returned. Therefore it is irrelevant what your SELECT does, as long it is valid. Here’s a very good example by Itzik Ben-Gan:
http://groups.google.de/group/micro…read/1c12caa50923d3d5/f86de13e0ed65a37?rnum=1 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Frank, Thanks for the information. Dan

Really Good Link Frank [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

]]>