Why EXISTS, why? - 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

Transact-SQL (2000)

Why EXISTS, why?

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-29 : 08:45:05

SET NOCOUNT ON

CREATE TABLE #temp1 (columna char(6), columnb int, columnc char(6))

INSERT #temp1 VALUES ('jason', 7, 'dood')

INSERT #temp1 VALUES ('jill', 9, 'doodet')

INSERT #temp1 VALUES ('joan', 6, 'bitch')

SELECT * FROM #temp1

CREATE TABLE #temp2 (columna char(6), columnb int)

INSERT #temp2 VALUES ('jason', 7)

INSERT #temp2 VALUES ('jill', 9)

INSERT #temp2 VALUES ('joan', 6)

SELECT * FROM #temp2

SELECT * FROM #temp1 S WHERE EXISTS (SELECT * FROM #temp2 T WHERE S.columna=T.columna)

DROP TABLE #temp1
DROP TABLE #temp2

columna columnb columnc
------- ----------- -------
jason 7 dood
jill 9 doodet
joan 6 bitch

columna columnb
------- -----------
jason 7
jill 9
joan 6

columna columnb columnc
------- ----------- -------
jason 7 dood
jill 9 doodet
joan 6 bitch

SELECT * FROM #temp1 S WHERE EXISTS (SELECT * FROM #temp2 T WHERE S.columna=T.columna)

Does this not read "in english" as get me the rows that from #temp1 that are in #temp2?

Voted best SQL forum nickname...."Tutorial-D"

Edited by - sitka on 05/29/2003 08:47:52

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-29 : 08:49:04

quote:

Does this not read "in english" as get me the rows that from #temp1 that are in #temp2?

I guess "INNER JOIN" doesn't either ... but what is the question?

Jay White
{0}

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-29 : 08:58:21

Logical leap here

DOES the above EXIST statement "read" as
'get me the rows from #temp1 that are in #temp2'

It seems obvious it dosen't but why not.
How would you explain it?

Voted best SQL forum nickname...."Tutorial-D"

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-29 : 09:07:57

Nevermind, me stoopid.

Voted best SQL forum nickname...."Tutorial-D"

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-29 : 09:16:01

quote:

It seems obvious it dosen't but why not.

Why does it seem obvious that it doesn't?

Sam

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-29 : 09:27:36

This is going nowhere fast but here goes.

quote:
Does this not read "in english" as get me the rows that from #temp1 that are in #temp2?

Well none of the rows in #temp1 EXIST IN (<--- English version) #temp2
therefore when confronted with this reality I accepted the obvious

quote:

It seems obvious it dosen't..

that my internal dialogue is off because As Henderson says

quote:
'EXISTS'.. It works very simply-if the subquery returns a result set- any result set- EXISTs returns True

Voted best SQL forum nickname...."Tutorial-D"

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-29 : 09:34:15

quote:

Well none of the rows in #temp1 EXIST IN (<--- English version) #temp2
therefore when confronted with this reality I accepted the obvious

EXISTS checks for the presence of anything (NOT NULL). Your query does find NON NULL results for each row because S.Columna=T.columna.

If you want to see the query return nothing, make the join fail like this: (I've added an 'x' character to the right side of the join)

SELECT * FROM #temp1 S WHERE EXISTS (SELECT * FROM #temp2 T WHERE S.columna=(T.columna + 'x'))

Hope this helps ?

Sam

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-29 : 10:50:52

Thanks SamC.
There is still a gap in the way I think about this.
Like where or what is 'EXISTS IN'.
And how would I talk about it.

Voted best SQL forum nickname...."Tutorial-D"

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-29 : 11:59:38

Not sure, but

EXISTS

and

IN

Are different and not used together??

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-29 : 22:01:41

Think of EXISTS like a function, kind of.

The argument it takes is a SQL expression.

If the expression returns ANY rows, then the EXISTS() pseudo-function returns TRUE.

If the rexpression returns NO rows, then it returns FALSE.

Does that clear things up a little?

EXISTS doesn't need to be used in WHERE clauses only; you can use it anywhere a boolean expression is valid.

WHILE EXISTS(SELECT * FROM SomeTable)
BEGIN
...
END

or

RETURN (CASE WHEN EXISTS(SELECT * FROM SomeTABLE) THEN 1 ELSE 0 END)

.etc....

- Jeff

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-30 : 08:00:24

quote:
Does that clear things up a little

Sure does.
Similar to.

quote:
'EXISTS'.. It works very simply-if the subquery returns a result set- any result set- EXISTs returns True

But I think it still begs the question
where or what is a mythical "EXISTS IN".
It seems so basic thing.
If EXISTS IN was not mythical

SELECT * FROM #temp1 S WHERE EXISTS IN (SELECT * FROM #temp2 T WHERE S.columna=T.columna)

Returns no rows.
because no rows of S (in total) exist in T.
maybe an IN Predicate that allowed more than a series?

"yeah...so what's the point?"
"Exactly"

Voted best SQL forum nickname...."Tutorial-D"

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-30 : 08:18:01

You may be mixing up a function and an operator incorrectly ?

WHERE EXISTS (SELECT STATEMENT)

AND

WHERE X IN (SELECT STATEMENT)

are both valid constructions.

EXISTS IN is not a valid combination of a function EXISTS and an operator IN.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources