October 1, 2009 at 12:20 pm
I don't understand why my WHERE EXISTS... doesn't work.
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#t1'))
DROP TABLE #t1;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#t2'))
DROP TABLE #t2;
create table #t1(x int)
insert #t1 select 1 union all select 2 union all select 3
create table #t2(x int)
insert #t2 select 2 union all select 3
select * from #t1
select * from #t2
select * from #t1 where exists (select * from #t2)
The output is
-------------
1
2
3
2
3
1
2
3
Question.
Isn't third result supposed to be:
2
3
I need equivalent of
select * from #t1 where x in (select x from #t2)
October 1, 2009 at 1:59 pm
The output is just what you asked for, which is if (SELECT * FROM #t2) exists. It does exist, so without any further WHERE criteria, everything from #t1 will be returned. Try this...
SELECT
*
FROM #t1
WHERE EXISTS (SELECT 'I exist')
Now, for your desired output, try this...
SELECT
t1.*
FROM #t1 t1 INNER JOIN
#t2 t2
ON t1.x = t2.x
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 1, 2009 at 2:11 pm
Thanks.
But I'm trying to learn how to use EXISTS.
Can you write me SQL with EXISTS
to get what I want?
October 1, 2009 at 2:18 pm
Well, if you really want to use the exists clause, you need to add a condition with the first table #T1.
Here's how you can do it:
SELECT *
FROM #t1
WHERE EXISTS (SELECT 1
FROM #t2
WHERE #t1.x = #t2.x)
That way, you use the data from the first table, and use it within your exists clause.
Bare in mind that this may not be performant, and people usually use joins since they are a lot easier to use.
You can see the returned value can be anything (in that scenario, i selected 1, but * could've been used, or anything)
Hope that helps,
Cheers,
J-F
October 1, 2009 at 4:32 pm
J-F Bergeron (10/1/2009)
Well, if you really want to use the exists clause, you need to add a condition with the first table #T1.Here's how you can do it:
SELECT *FROM #t1
WHERE EXISTS (SELECT 1
FROM #t2
WHERE #t1.x = #t2.x)
That way, you use the data from the first table, and use it within your exists clause.
Bare in mind that this may not be performant, and people usually use joins since they are a lot easier to use.
You can see the returned value can be anything (in that scenario, i selected 1, but * could've been used, or anything)
Hope that helps,
Despite it "looking" like RBAR, every test I've seen has put this method as just as performant (I believe the optimizer just converts it to a join, not near a SQL server to test ATM tho), but I agree on the readability.
riga1966
But I'm trying to learn how to use EXISTS.
To expand on what the posters above me were explaining, EXISTS is true/false only. It does not return data, it returns true, or false. In your original example, there was data in the table, thus exists returned true and all data from 1 was returned.
The difference in the above example is that it links to the original table and does an exists check for every line. Lines that are there return true and are in the result set, lines that aren't return false and are not in the result set.
This is the reason you can use SELECT 1, or SELECT *, or SELECT [randomfield] etc. It's not actually returning that data anyways, it's just checking to see if something is there.
October 2, 2009 at 6:51 am
Bingo!
Thanks a lot Garadin!
"..EXISTS is true/false only. It does not return data, it returns true, or false..."
This explains everyhitng.
I thought it returns data and works like WHERE ID IN (Select ....)
Thank you very much Garadin!
October 2, 2009 at 6:58 am
riga1966 (10/2/2009)
Bingo!Thanks a lot Garadin!
Blehh... well thanks! Hehe! :hehe:
Cheers,
J-F
October 2, 2009 at 7:25 am
To clarify what J-F said. You would use a JOIN when you want to return data from #t2 and you might use WHERE EXISTS when you only want to use #t2 to filter results from #t1.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply