sub-nested EXISTS isn't working as expected

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

Old Forums

CLOSED - General SQL Server

sub-nested EXISTS isn't working as expected

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-18 : 07:26:50

Robert writes "Hello SQL_Team,

Do you have any experts on the EXISTS SQL statement. This is a brain twister.

Table EX1, Column ‘AA’ Values 1,2,3,4
Table EX2, Column ‘BB’ Values 1,2,3,4,5,6

Query EX_Q0
SELECT AA FROM EX1
>> returns 1,2,3,4

Query EX_Q1
SELECT BB FROM EX2 WHERE BB IN (1,2,3,4);
>> returns 1,2,3,4

Query EX_Q2
SELECT BB FROM EX2 WHERE EXISTS (SELECT AA FROM EX1);
>> returns 1,2,3,4,5,6

Using the second query to get the values IN the list, I get expected results 1,2,3,4.
Using the third query with an EXISTS statement, I expect the same results but instead get ALL columns from table BB.

I’m assuming there is something about EXISTS that I don’t understand.

What do you think? Is it possible to use EXISTS and sub-queries this way? I of course can look at doing a join for this case but I can't for another more complicated set of tables that kicked off this simplified test.

Regards,
Robert"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-18 : 08:09:50

A very good question.

Both EXISTS and IN perform similar task.

The reason we have two keywords that perform the same task is performance. When using EXISTS, SQL Server doesn't have to do too much and does not perform a row by row join. It looks through the record set and returns TRUE when the first occurrence is found.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-18 : 08:17:32

You don't have any correlation between the subquery and the main query. So the subquery is always true as long as there is a row in EX1.

SELECT BB FROM EX2 WHERE EXISTS (SELECT * FROM EX1 WHERE AA = BB);

~~The other response is bollocks.~~
Sorry, that was uncalled-for.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-18 : 08:28:34

Brain-twister...yeah, right!

Owais

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-08-18 : 08:31:19

quote:

Brain-twister...yeah, right!

Owais, do you not approve of people asking questions that they don't know the answer to ?

Damian

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-18 : 08:32:47

quote:

~~The other response is bollocks.~~
Sorry, that was uncalled-for.

No, it was defintely uncalled for because EXISTS is performance related.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-18 : 08:50:31

quote:
Originally posted by Amethystium
No, it was defintely uncalled for because EXISTS is performance related.

Ahh, but it's not really the same as IN is it?


create table #moo (mooness int null)
insert into #moo values (1)
insert into #moo values (2)
SELECT mooness from #moo where mooness in (1,3)
go
SELECT mooness from #moo where exists (1,3)
go
drop table #moo

So while you might want to use exists to quickly scan through a table for a certain value, I'm not sure that you can use it to filter in the same way as IN.

-------
Moo. :)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-18 : 09:00:51

Oops, I think I set off some sarcasm detectors...

No Damian, I am not trying to discourage people from asking questions, just poking a little (harmless) fun at how people get so confused at simple issues.

I think people need to try and help themselves first, before asking somebody else for help...there are plenty of places where he could have checked the syntax and requirements for EXISTS, the least of which is Books Online.

Owais

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-08-18 : 09:06:43

quote:

I think people need to try and help themselves first, before asking somebody else for help

This is an interesting point... on one hand I agree, on the other... if everybody figured out the answers, there would be nothing to answer here and we wouldn't have a community.

Don't be too harsh, at least this poster has done some experimenting first.

quote:

the least of which is Books Online

I think it's one of the best

Damian

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-18 : 09:10:01

Cheers!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-18 : 09:10:31

Halaa Owais, keefak el-youm?

quote:

I think people need to try and help themselves first, before asking somebody else for help...there are plenty of places where he could have checked the syntax and requirements for EXISTS, the least of which is Books Online.

Yes in some cases and no in others.
Actually BOL does not offer a simple explanation of why there is an EXIST keyword when we could use the IN clause to similar effect.
As I said before. EXISTS is there for performance purpose and perhaps the original poster was not aware of that.

Don't forget that this is a an SQL website and not a place to tell people when to ask a question. You would be amazed at how much team people (including myself) waste time in search of simple answers. The way I see it is this, try and solve your problem but don't waste too much time when there websites full of people wanting to help out others.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-18 : 09:24:43

hala, keefak?

I think the only point that the poster missed was that he needed a correlation between the inner and outer queries. He wasn't concerned about performance, he just wanted to know why IN and EXISTS returned different results...which was because his EXISTS syntax was incorrect.

Of course, as Damian said we have to give him credit for trying something on his own

Owais

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-18 : 19:38:50

quote:
No, it was defintely uncalled for because EXISTS is performance related.

I have to agree with Arnold here and say that is bollocks.

EXISTS is a existential quantifier from relational calculus which is closely related to predicate calculus.

IN is really just short hand for the relational algebra OR operator.
The fact that IN can be used for subqueries is just a bonus...

The "Result Value" section in BOL for each operator shows the fundamental difference....

DavidM

"SQL-3 is an abomination.."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-19 : 07:32:58

quote:
Both EXISTS and IN perform similar task.

This makes me uncomfortable too, byrmol. Consider ....


create table ex1 (aa int)
create table ex2 (bb int)

insert ex1 select 1 union select 2 union select null union select 4
insert ex2 select 1 union select 2 union select 3 union select 4
                    union select 5 union select 6

Write a query to give the relational difference between ex2 and ex1 (give me all the bbs in ex2 that are not aas in ex1) ...


select
    *
from
    ex2
where
    bb not in (
        select
            aa
        from
            ex1)

select
    *
from
    ex2 e
where
    not exists (
        select
            aa
        from
            ex1
        where
            e.bb = aa)

See the difference?

Jay White
{0}

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-19 : 08:11:31

quote:

I have to agree with Arnold here and say that is bollocks.

SELECT CustomerID, CompanyName
FROM Customers cu
WHERE EXISTS
(SELECT OrderID
FROM Orders o
WHERE o.CustomerID = cu.CustomerID)

is the same as :


SELECT DISTICT cu.CustomerID, cu.CompanyName
FROM Customers
JOIN Orders o
ON cu.CustomerID = o.CustomerID

Why use EXISTS? because it is performance. When using EXISTS SQL Server does NOT have to do a row by row join. Instead it look sthrough thr records until it finds the first match and stop there and then. As soon as there is a single match, EXISTS returns TRUE so there is no need to go further.

... and practice what you guys preach, i.e. don't be so rude.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-19 : 08:15:21

quote:
Originally posted by Page47

quote:
Both EXISTS and IN perform similar task.

This makes me uncomfortable too, byrmol. Consider ....

See the difference?

I said similar and not the same.

And I guess you added the NULL right at the end to prove the point.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-19 : 08:26:47

The two have identitcal execution plans?

The IN and EXISTS predicates are different and a JOIN is different too. They do different things. Sure, you may be able to use any of the three to extract the same rowset sometimes, since thier functionalities overlaps some, but don't confuse that for them being the same except for performance. As you decompose the meaning of IN and EXISTS the fundamental difference become more and more clear, highlighted (to some degree) by my above post. There is much more to making the correct predicate choice than performance. After all, if an EXISTS predicate could do all the things an IN and a JOIN can do, and do it faster, then why would the other two ... uhm ... exist?

... don't confuse disagreement with rudeness.

Jay White
{0}

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-19 : 09:25:56

quote:

Originally posted by Page47


create table ex1 (aa int)
create table ex2 (bb int)

insert ex1 select 1 union select 2 union select null union select 4
insert ex2 select 1 union select 2 union select 3 union select 4
                    union select 5 union select 6

Write a query to give the relational difference between ex2 and ex1 (give me all the bbs in ex2 that are not aas in ex1) ...


select
    *
from
    ex2
where
    bb not in (
        select
            aa
        from
            ex1)

select
    *
from
    ex2 e
where
    not exists (
        select
            aa
        from
            ex1
        where
            e.bb = aa)

See the difference?

Jay White

Jay, the results I saw were quite unexpected. The IN query did not return any rows at all! However, if ex1 does not contain a NULL value, then both queries return the same resultset. What's the reason behind this?

Owais

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-19 : 10:13:37

quote:
What's the reason behind this?

The reason behind this is the definition of the IN predicate.


where
    bb not in (
        select
            aa
        from
            ex1)

breaks down as follows ...

where bb not in (1,2,null,4)
where not (bb in (1,2,null,4))
where not ((bb = 1) or (bb = 2) or (bb = null) or (bb = 4))
where ((bb <> 1) AND (bb <> 2) AND (bb <> NULL) AND (bb <> 4))
where ((bb <> 1) AND (bb <> 2) AND UNKNOWN AND (bb <> 4))
where UNKNOWN

It is when you decompose the IN predicate like this you begin to see just how different IN and EXISTS and a JOIN are. It is valid to compare performance of same (at least similar) operations, but saying apples are red'er than oranges doesn't help me out when I want to make some fresh squeezed orange juice.

Jay White
{0}

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-19 : 10:51:30

Ok, so I should have said that they are a little bit different instead of using the word similar.

... in other words, bollocks to all who disagree.


SELECT c.CustomerID, CompanyName
FROM Customers c
LEFT OUTER JOIN Orders o
ON C.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL

Could be re-written using EXISTS. And I can guarantee that the performance of using EXISTS (well with a NOT obviously) would be much better than a join.

and it's the best thing when you're checking for the existance of an object etc..

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-19 : 13:15:33

quote:
but saying apples are red'er than oranges doesn't help me out
when I want to make some fresh squeezed orange juice

Yes!! Yet the same case with saying apples are NOT red'er than oranges...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources