May 23, 2012 at 8:35 am
Hi All
Consider the following 2 Tables:
CREATE TABLE [dbo].[T1](
[Col1] [int] NOT NULL,
[Col2] [int] NOT NULL)
GO
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (1, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (2, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (3, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (4, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (5, 2)
INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (22, 44)
GO
CREATE TABLE [dbo].[T2](
[Col1] [int] NOT NULL,
[Col2] [int] NOT NULL
)
GO
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (1, 2)
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (2, 2)
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (3, 2)
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (4, 2)
INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (5, 2)
Then consider the following query:
select * from T1 TT
where exists (select Col1 from T2 TTT where TTT.Col1 = TT.Col1)
I understood that when using EXISTS, SQL checks if any rows are returned by the sub-query, if anything is returned then the outer query will return everything(*) - If not, then nothing will be returned.
I always understood that it works with true/false..
When you execute select * from T1 TT
where exists (select Col1 from T2 TTT where TTT.Col1 = TT.Col1)
It doesn't return everything in the T1.
Why is this?
Thanks
May 23, 2012 at 9:02 am
Your query:
select
*
from
T1 TT
where
exists (select
Col1
from
T2 TTT
where
TTT.Col1 = TT.Col1);
Does not retunr the values 22 and 44 because the value 22 in table T1 does not exist in table T2.
The query in the exists is a corralted query and is evaluated for each row in table T1 against all rows in table T2 where Col1 in T1 equals Col1 in T2.
May 23, 2012 at 10:44 am
Lynn Pettis (5/23/2012)
Your query:
select
*
from
T1 TT
where
exists (select
Col1
from
T2 TTT
where
TTT.Col1 = TT.Col1);
Does not retunr the values 22 and 44 because the value 22 in table T1 does not exist in table T2.
The query in the exists is a corralted query and is evaluated for each row in table T1 against all rows in table T2 where Col1 in T1 equals Col1 in T2.
Understood
Is my query the same as
select TTT.Col1,TT.Col2 from T1 TT
inner join T2 TTT
on TT.Col1 = TTT.Col1
and TT.Col2 = TTT.Col2
Would my previous understanding be correct is this example?
select
*
from
T1 where exists(select * from sys.databases where name = 'AdventureWorks')
For the above, the sub-query has to be true or false:
True: Return * from T1
False: Return nothing from T1
Where am I going wrong here?
Thanks
May 23, 2012 at 10:53 am
derekr 43208 (5/23/2012)
select*
from
T1 where exists(select * from sys.databases where name = 'AdventureWorks')
For the above, the sub-query has to be true or false:
True: Return * from T1
False: Return nothing from T1
Where am I going wrong here?
Thanks
This is NOT a correlated subquery, it does not reference the outer table. This query will return everything from T1 if there is a row in sys.databases for AdventureWorks. It will return nothing if there is not.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 10:55 am
See if this wiki article will help in your understanding. Not a very in depth look but it may help you get what is going on.
http://en.wikipedia.org/wiki/Correlated_subquery
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 10:59 am
Sean Lange (5/23/2012)
See if this wiki article will help in your understanding. Not a very in depth look but it may help you get what is going on.
Thanks
I'm trying to get my head around the EXISTS, IN , EXISTS vs IN subject
Thanks
Derek
May 23, 2012 at 11:02 am
derekr 43208 (5/23/2012)
Sean Lange (5/23/2012)
See if this wiki article will help in your understanding. Not a very in depth look but it may help you get what is going on.Thanks
I'm trying to get my head around the EXISTS, IN , EXISTS vs IN subject
Thanks
Derek
Try Gail's blog. http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 11:05 am
CELKO (5/23/2012)
Thanks for the DDL, but it has errors. Tables need keys and you wrote needless dialect, and no semi-colons. Try this, using current T-SQL instead of the old Sybase code museumCREATE TABLE T1]
([col1] INTEGER NOT NULL PRIMARY KEY,
[col2] INTEGER NOT NULL);
INSERT INTO [T1]
VALUES (1, 2),(2, 2),(3, 2),
(4, 2),(5, 2),(22, 44);
GO
CREATE TABLE T2
(col1] INTEGER NOT NULL PRIMARY KEY,
[col2] INTEGER NOT NULL);
INSERT INTO T2
VALUES (1, 2),(2, 2),(3, 2),
(4, 2),(5, 2);
Now, we do not use SELECT * in production code. The exception is the EXISTS() predicate where it is preferred. It show that the SELECT is not a query but a subquery parameter that does not return data. These are little things that separate good SQL programers from mediocre ones.
SELECT col1, col2
FROM T1
WHERE EXISTS
(SELECT *
FROM T2
WHERE T2.col1 = T1.col1);
>> I understood that when using EXISTS, SQL checks if any rows are returned by the sub-query, if anything is returned then the outer query will return everything(*) - If not, then nothing will be returned.<<
No. It is like any other predicate, each row in gets tested. When I get to T1.col1 = 22, it fails.
>> I always understood that it works with TRUE/FALSE. <<
Correct, there is no UNKNOWN for this guy. You might want to look at table level operations (EXCEPT, UNION, INTERSECT) instead of row level predicates.
You know Mr. Celko, the majority of your post is totally irrelevent to what the OP was asking. He is trying to understand how the EXIST predicate works as it isn't working the way he thought it should in this query. He apparently didn't understand that the query inside the EXISTS was a correlated subquery and how those work.
Please, try to focus and actually help someone to learn instead of being such an arrogant, self absorbed, intellectual noob. If you can't, then please just go away and leave the rest of us in peace.
May 23, 2012 at 11:24 am
Lynn Pettis (5/23/2012)
Your query:
select
*
from
T1 TT
where
exists (select
Col1
from
T2 TTT
where
TTT.Col1 = TT.Col1);
Does not retunr the values 22 and 44 because the value 22 in table T1 does not exist in table T2.
The query in the exists is a corralted query and is evaluated for each row in table T1 against all rows in table T2 where Col1 in T1 equals Col1 in T2.
Thanks
Considering the below 2 queries:
select
*
from
T1 TT
where
exists (select
Col1
from
T2 TTT
where
TTT.Col1 = TT.Col1)
and
select
*
from
T1 TT
where
exists (select
*
from
T2
where
Col1 = '2')
Does the WHERE EXISTS behave differently between these 2 queries
If so, please explain
Thanks
May 23, 2012 at 11:29 am
derekr 43208 (5/23/2012)
Lynn Pettis (5/23/2012)
Your query:
select
*
from
T1 TT
where
exists (select
Col1
from
T2 TTT
where
TTT.Col1 = TT.Col1);
Does not retunr the values 22 and 44 because the value 22 in table T1 does not exist in table T2.
The query in the exists is a corralted query and is evaluated for each row in table T1 against all rows in table T2 where Col1 in T1 equals Col1 in T2.
Thanks
Considering the below 2 queries:
select*
from
T1 TT
where
exists (select
Col1
from
T2 TTT
where
TTT.Col1 = TT.Col1)
and
select*
from
T1 TT
where
exists (select
*
from
T2
where
Col1 = '2')
Does the WHERE EXISTS behave differently between these 2 queries
If so, please explain
Thanks
Yes. As I explained earlier, the first query has a correlated subquery in the EXISTS predicate. This query is run for each row in the outer table returning true or false. Only the rows where exists is true is returned.
The second query has an independent query, it checks to see if row in Col1 in table T2 has the value '2', if it does the EXISTS is true and all the rows from table T1 will be returned.
May 23, 2012 at 11:54 am
Yes. As I explained earlier, the first query has a correlated subquery in the EXISTS predicate. This query is run for each row in the outer table returning true or false. Only the rows where exists is true is returned.
The second query has an independent query, it checks to see if row in Col1 in table T2 has the value '2', if it does the EXISTS is true and all the rows from table T1 will be returned.
Thanks - Getting There!!!
Is the first Query, the one with the Correlated Subquery the same as:
select TTT.Col1,TT.Col2 from T1 TT
inner join T2 TTT
on TT.Col1 = TTT.Col1
and TT.Col2 = TTT.Col2
Thanks
Derek
May 23, 2012 at 12:10 pm
derekr 43208 (5/23/2012)
Yes. As I explained earlier, the first query has a correlated subquery in the EXISTS predicate. This query is run for each row in the outer table returning true or false. Only the rows where exists is true is returned.
The second query has an independent query, it checks to see if row in Col1 in table T2 has the value '2', if it does the EXISTS is true and all the rows from table T1 will be returned.
Thanks - Getting There!!!
Is the first Query, the one with the Correlated Subquery the same as:
select TTT.Col1,TT.Col2 from T1 TTinner join T2 TTT
on TT.Col1 = TTT.Col1
and TT.Col2 = TTT.Col2
Thanks
Derek
No you have more columns and you have also added "and TT.Col2 = TTT.Col2" as a second condition. If you removed that and made the query
select TT.*
from T1 TT
inner join T2 TTT
on TT.Col1 = TTT.Col1
Then they would be the same.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 12:10 pm
derekr 43208 (5/23/2012)
Yes. As I explained earlier, the first query has a correlated subquery in the EXISTS predicate. This query is run for each row in the outer table returning true or false. Only the rows where exists is true is returned.
The second query has an independent query, it checks to see if row in Col1 in table T2 has the value '2', if it does the EXISTS is true and all the rows from table T1 will be returned.
Thanks - Getting There!!!
Is the first Query, the one with the Correlated Subquery the same as:
select TTT.Col1,TT.Col2 from T1 TTinner join T2 TTT
on TT.Col1 = TTT.Col1
and TT.Col2 = TTT.Col2
No, more like this:
select
TTT.Col1,
TT.Col2
from
T1 TT
inner join T2 TTT
on TT.Col1 = TTT.Col1
Thanks
Derek
May 23, 2012 at 12:24 pm
Lynn Pettis (5/23/2012)
derekr 43208 (5/23/2012)
Yes. As I explained earlier, the first query has a correlated subquery in the EXISTS predicate. This query is run for each row in the outer table returning true or false. Only the rows where exists is true is returned.
The second query has an independent query, it checks to see if row in Col1 in table T2 has the value '2', if it does the EXISTS is true and all the rows from table T1 will be returned.
Thanks - Getting There!!!
Is the first Query, the one with the Correlated Subquery the same as:
select TTT.Col1,TT.Col2 from T1 TTinner join T2 TTT
on TT.Col1 = TTT.Col1
and TT.Col2 = TTT.Col2
No, more like this:
select
TTT.Col1,
TT.Col2
from
T1 TT
inner join T2 TTT
on TT.Col1 = TTT.Col1
Thanks
Derek
Thanks
To Summarize, and correct me if I am wrong
When dealing with WHERE EXISTS, in the case where there is a correlated sub-query: The sub-query has its results and the outer query has it's own results, the results of both are cross referenced and only the matching rows/values are returned.
When dealing with WHERE EXISTS, in the case where the sub-query is independent from the outer query, when the sub-query returns true, the requested results from the outer query are returned
Is this correct?
My terminology might be wrong, I just need to check if I'm getting the process right
Thanks
Derek
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply