SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Subquery


Subquery

Author
Message
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2138 Visits: 2931
Hi guys

Trying to gear up my T-SQL skills

When dealing with subqueries, like below

select Col1 from Tbl1 where Col1 = '52' and Col1 in (select Col2 from Tbl2)



What is the order of the execution here? Does SQL first check for the value 52 in Tbl2.Col2 and then check for the value 52 in Tbl1.Col1

Thanks
Roland Alexander STL
Roland Alexander STL
Say Hey Kid
Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)

Group: General Forum Members
Points: 677 Visits: 466
Maybe, maybe not. Depends on many factors such as indexing, clustering, number of rows in the tables, etc. Check the execution plan to see what's getting processed when. Might surprise you!

Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2138 Visits: 2931
Roland Alexander STL (11/9/2012)
Maybe, maybe not. Depends on many factors such as indexing, clustering, number of rows in the tables, etc. Check the execution plan to see what's getting processed when. Might surprise you!


Thanks for the reply

I want to be able to completely understand what is fundamentally happening here

The subquery has its results and the outer query has its own results as well, are these 2 results cross referenced?

Thanks
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26816 Visits: 17557
I would write that query a little differently. I would instead write it as


select t1.Col1
from Tbl1 t1
join Tbl2 t2 on t1.Col1 = t2.Col2
where t1.Col1 = '52'



That to me is a LOT easier to read and understand quickly. Check out the execution plans for both queries. It will really surprise you!!!

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2138 Visits: 2931
Sean Lange (11/9/2012)
I would write that query a little differently. I would instead write it as


select t1.Col1
from Tbl1 t1
join Tbl2 t2 on t1.Col1 = t2.Col2
where t1.Col1 = '52'



That to me is a LOT easier to read and understand quickly. Check out the execution plans for both queries. It will really surprise you!!!


Thanks Sean

I'm just trying to better understand T-SQL

When dealing with correlated subqueries, are these 2 queries essentially the same?

--1
select T1.Col1 from T1
where exists
(select Col1 from T2 where T2.Col1 = T1.Col1)
--2
select T1.Col1 from T1
where T1.Col1 in
(select Col1 from T2 where T2.Col1 = T1.Col1)



Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90267 Visits: 45284
Well, those aren't written correctly. The correct form would be

--1
select T1.Col1 from T1
where exists
(select * from T2 where T2.Col1 = T1.Col1)
--2
select T1.Col1 from T1
where T1.Col1 in
(select T2.Col1 from T2)



And those two are completely equivalent.

http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2138 Visits: 2931
GilaMonster (11/12/2012)
Well, those aren't written correctly. The correct form would be

--1
select T1.Col1 from T1
where exists
(select * from T2 where T2.Col1 = T1.Col1)
--2
select T1.Col1 from T1
where T1.Col1 in
(select Col1 from T2)



And those two are completely equivalent.

http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/


Thanks Gail

I think I'm getting there

When using in adding a correlation predicate is redundant?

In my own words, using the query below. The subquery has already satisfied what I'm looking therefore rendering the outer query unnecessary.
Something like select Col1 from table1 where table1 in (select Col1 from table1 )

Am I on track here?

select T1.Col1 from T1
where T1.Col1 in
(select Col1 from T2 where T2.Col1 = T1.Col1)



Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90267 Visits: 45284
SQLSACT (11/12/2012)
When using in adding a correlation predicate is redundant?


Written correctly, just about all the time. Please read that blog post and maybe BoL on how the IN function works

Am I on track here?

select T1.Col1 from T1
where T1.Col1 in
(select Col1 from T2 where T2.Col1 = T1.Col1)



No, I gave you the correct form in my previous post.

--2
select T1.Col1 from T1
where T1.Col1 in (select T2.Col1 from T2)


Think about what the IN does with the two columns specified and you should understand why the correlation is completely unnecessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2138 Visits: 2931
GilaMonster (11/12/2012)
SQLSACT (11/12/2012)
When using in adding a correlation predicate is redundant?


Written correctly, just about all the time. Please read that blog post and maybe BoL on how the IN function works

Am I on track here?

select T1.Col1 from T1
where T1.Col1 in
(select Col1 from T2 where T2.Col1 = T1.Col1)



No, I gave you the correct form in my previous post.

--2
select T1.Col1 from T1
where T1.Col1 in (select Col1 from T2)


Think about what the IN does with the two columns specified and you should understand why the correlation is completely unnecessary.


Thanks

I see now why it's unnecessary

When I asked "am I on track here" followed by the query. I was just confirming that that query was incorrect based on my Layman's terms.

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search