Sql Exists 1

[miclus]

Can anyone explain what this statement does?

Select * from X where exists (select Id from Y)

 

[PHV]

Select all columns from table X provided table Y is not empty.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[miclus]

Thanks. The exists confuses me. Can you explain sort of the steps of how that query is working?

 

[PHV]

The EXISTS predicate returns a boolean value saying if the following subquery returns something or not.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gmmastros]

Think of Exists() as a function that returns a boolean. If the 'select' within the exists returns any rows, then exists returns true. If there are no rows selected within the exists, then it returns false.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[miclus]

Ok. But, some confuse me like say this:

Select * from X where exists (Select * from Y where X.id = Y.id)

I don't see how it's actually filtering out stuff...

 

[gmmastros]

That syntax would be functionally equivalent to:

Select X.*
From   X Inner Join Y On X.id = Y.Id

Only rows from X will be returned if there are also rows in Y that have the same id.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

Select * from X where exists (Select * from Y where X.id = Y.id)
Does the same:
SELECT DISTINCT X.* FROM X INNER JOIN Y ON X.id = Y.id
Or:
SELECT * FROM X WHERE id IN (SELECT id FROM Y)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

gmmastros, your SQL code is NOT functionally equivalent to miclus's SQL code (unless Id is never duplicated in Y).

 

[miclus]

Wow, now I see. Thanks dudes.

 

[gmmastros]

PHV,

I really thought I knew my stuff. It just goes to show that there is always more to learn. I did, of course, test this (just for my own sanity). Here's how.

Declare @X Table(Id Integer, Name VarCHar(10))

Insert Into @X Values(1, 'george')

Declare @Y Table(Id Integer, Color VarChar(20))
Insert Into @Y Values(1, 'green')
Insert Into @Y Values(1, 'blue')
Insert Into @Y Values(1, 'red')

[green]-- The original query[/green]
Select * from @X X Where Exists(Select * From @Y Y Where X.Id = Y.Id)

[green]-- What I mistakenly thought it would be.[/green]
Select X.*
From   @X X inner Join @Y Y On X.Id = Y.Id

Thanks for pointing that out to me. You get a star for that.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[miclus]

So, guys, is there a way to write my original one using "in"? This one:

Select * from X where exists (select Id from Y)

 

[Golom]

As PHV said, your original returned all records from X if there were any records in Y AND it returned nothing if Y contains no records.

EXISTS and IN operate differently and are intended for different purposes.

As George said, EXISTS returns a boolean indicating that records do (TRUE) or do not (FALSE) exist in the sub-query. EXISTS does not return any information about which records exist or how many of them there are.

In contrast, the sub-query in an IN clause returns a table containing values in one or more fields that you can compare for equality or inequality to fields in the main query. Some RDBMS allow only one field to be returned by an IN sub-query while others allow multiple fields.

In short, No ... there's no obvious way to make an IN clause produce exactly the same effect that the EXISTS clause you posted would produce.