exists VS where id in(select id from... )

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 VS where id in(select id from... )

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-12-04 : 16:34:49

Hi,

I am having difficulty to understand the difference between these two queries, sometimes the second version stalls and takes an incredible amount of time to execute what the first version does in 1 or 2 seconds,plus in a previous post someone suggested version 1

--version1

select *
from [myDB1]..myTable A
where not exists (select * from anothertable B where B.id = A.id)

--AND

--version 2

select *
from [myDB1]..myTable A
where not A.id in (select B.id from anothertable B where B.id = A.id)

thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-04 : 16:50:53

The EXISTS operator just checks for existence of one row that would satisfy the given subquery (without even returning that one row) and then returns TRUE.

The IN operator runs the entire subquery, returning all the rows in the subquery, then it checks the values returned by the subquery to see if the value you want is in the list and if it is returns TRUE.

So IN may have much more work to do and you should almost always use EXISTS instead of IN with subqueries.

See in Books Online - "Using EXISTS and NOT EXISTS to Find Intersection and Difference" (2000) or "Subqueries with EXISTS" and "Subqueries with NOT EXISTS" (2005)

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 00:43:00

If

select B.id from anothertable B

returns UNIQUE values of B.id, then I reckon you are better with a JOIN (even if you don't use any values from TABLE_B). I don't think its any more efficient, but it isn;t any less!, but it may be that it just suits my style of coding and code presentation.

Where that isn't the case and TABLE_A has a complex where clause, lots of JOINed tables, etc etc. and TABLE_B is large (such that the subquery is slow, as snSQL points out) I usually repeat the criteria from the main query into the sub query to "narrow" its scope:


select *
from [myDB1]..myTable A
where A.id not in 
     (
         select B.id 
         from   anothertable B
                JOIN myTable A
                    ON A.id = B.id
     )

as I have found that the Optimiser tends to handle this better. But its basically the same as a NOT EXISTS.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 01:09:19

What about this?

select a.*
from myTable A
left join anothertable B on B.id = A.id
where b.id is null

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 01:18:56

If B.id is NOT unique it will give you multiple copies of a.*

But other than that that would be my choice. Although I do think for newbies its harder to "read" than:

NOT EXISTS ...

or

A.id NOT IN (SELECT B.id FROM ...

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 01:23:34

True enough!

I was more thinking speed-wise

Peter Larsson
Helsingborg, Sweden

pootle_flump

1064 Posts

Posted - 2006-12-05 : 04:20:21

If you stick a distint into Peso's query you will get the same plan as an exists statement. Once the query starts getting more complex then you can't use that trick but for something so simple the optimiser figures it out IME.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-05 : 08:45:19

Don' forget the main difference between NOT IN(<subquery>) and NOT EXISTS(<correlated subquery>) is nulls.

declare @t table(i int)
insert into @t select 1 as i union select 2

select * from @t where i not in (select 1 as i union select null)
select * from @t where not exists (select 1 where i = 1 union select 1 where i = null)

Think of it this way. I want to make a ham&cheese sandwich, for which I need bread, ham, cheese and mayo. I have ham, mayo and SOME OTHER UNKNOWN STUFF(NULL) in the fridge. Make me a store list.

Jay
to here knows when

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 08:59:50

[code]-- prepare test data
declare @t1 table(i int)
insert into @t1 select 1 union all select 2 union all select null

declare @t2 table(i int)
insert into @t2 select null union all select 2 union all select 3

-- matches
select a.i 'Matching with IN' from @t1 a where a.i in (select b.i from @t2 b)
select a.i 'Matching with EXISTS' from @t1 a where exists (select * from @t2 b where b.i = a.i)

-- no hits with IN
select a.i 'Not matching with IN' from @t1 a where a.i not in (select b.i from @t2 b)
select a.i 'Not matching with IN, using non-null values' from @t1 a where a.i not in (select b.i from @t2 b where b.i is not null)

-- no hits with EXISTS
select a.i 'Not matching with EXISTS' from @t1 a where not exists (select b.* from @t2 b where b.i = a.i)
select a.i 'Not matching with EXISTS, using non-null values' from @t1 a where not exists (select b.* from @t2 b where b.i = a.i and b.i is not null)[/code]

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-05 : 10:10:10

I'm not really so good with logic, but I'm pretty sure it works like this

--I've got ham(2), mayo(3) and some other stuff(null) in the fridge. Do I have ham(2) in the fridge?
where a.i in (select b.i from @t2 b)
where {2} in {null, 2, 3}
where (2 = null) or (2 = 2) or (2 = 3)
where UNKNOWN or TRUE or FALSE
where TRUE

--I've got ham(2), mayo(3) and some other stuff(null) in the fridge. Do I need to buy more cheese(1)?
where a.i not in (select b.i from @t2 b)
where {1} not in {null, 2, 3}
where not ((1 = null) or (1 = 2) or (1 = 3))
where not (1 = null) and not (1 = 2) and not (1 = 3)
where not (UNKNOWN) and not (FALSE) and not (FALSE)
where UNKNOWN and TRUE and TRUE
where UNKNOWN

Jay
to here knows when

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources