"Exists" v/s "In" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

"Exists" v/s "In"

Author

Topic

ashay
Starting Member

1 Post

Posted - 2002-03-19 : 00:58:40

hi,

i am not able to figure out the difference between the "Exists" and "In" clause.At one of the sites (www.sql-server-performance.com)it was mentiooned that always use "Exists" instead of "In" wherever
possible....but the exact reason was not given.

Can anyone throw some light on the same....

regards,
ashay

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-03-19 : 01:28:43

The BOL says:
Notice that subqueries introduced with EXISTS are a bit different from other subqueries in these ways:

-The keyword EXISTS is not preceded by a column name, constant, or other expression.
-The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are simply testing for the existence of rows that meet the conditions specified in the subquery.
HTH

------------------
If I am to do it,
I will, and NOW ..

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-20 : 21:40:31

I did some measurements on this a while ago - v7 sp2 I think
There is generally not much difference between

select * from tbl
where col in (select col2 from tbl2)

select * from tbl
where exists (select * from tbl2 where col = col2)

select distinct tbl.* from tbl, tbl2
where col = col2

In fact the in clause was slightly faster than the others.

The problem comes when doing updates or using compound keys
update tbl set colu = 2
where col in (select col2 from tbl2)
was much slower than the exists example.

also
select * from tbl
where exists (select * from tbl2 where col = col2 and cola = col3)
is more difficult to simulate in an in clause.
I have seen (rewritten) systems where this was done by converting the fields to character and concatenating.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources