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


Select in select subquery


Select in select subquery

Author
Message
bengosul
bengosul
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
I apologize for this not being specific to SQL 2012, but rather SQL in general, I ran into an issue with a statement that I expected would throw an error and yet it runs in an unexpected way. Basically the subquery should select a column that does not exist in the table referenced in it, yet it somehow runs. Below are the steps to reproduce:


create table test1 (firstname1 varchar(10), lastname1 varchar(10))
create table test2 (firstname2 varchar(10), lastname2 varchar(10))

insert test1 values ('Larisa', 'Brown')
insert test2 values ('John', 'Chaplan')

select * from test1
where firstname1 in (select firstname1 from test2)



Even though there is no column firstname1 in table test2, this query returns all rows in the table test1 and works as long as the subquery uses a column name defined in the first table and the second table is not empty. Prepending the proper table name to the subquery column (table2.firstname1) returns an error as expected, however why doesn't it return an error without it?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17216 Visits: 19557
It's to support correlated subqueries like this:

select * 
from test1 t1
where t1.firstname1 in (select t2.firstname2 from test2 t2 where t2.firstname2 = t1.firstname1)




and is equivalent to this:
select * 
from test1 t1
where t1.firstname1 in (select t1.firstname1 from test2 t2)




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13253 Visits: 8566
I have an example almost identical to this in my Common TSQL Mistakes session I have given about 75 times now. It represents the WORST king of data processing error - the WRONG OUTPUT/EFFECT with NO WARNING!! The solution (and general best practice in any case) is to ALWAYS use aliases for EVERYTHING. If you do, then your code will BREAK when you try to reference t2.mycolumn when mycolumn doesn't exist on the inner table. See Chris' example...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
pietlinden
pietlinden
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5194 Visits: 13213
Kevin,

I'm really good at making T-SQL mistakes. Is the presentation posted somewhere so I can learn from someone else's mistakes instead of just my own?

Thanks!
Pieter
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17216 Visits: 19557
TheSQLGuru (8/28/2013)
...general best practice in any case) is to ALWAYS use aliases for EVERYTHING...


And again, just in case it was missed the first time around.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13253 Visits: 8566
pietlinden (8/28/2013)
Kevin,

I'm really good at making T-SQL mistakes. Is the presentation posted somewhere so I can learn from someone else's mistakes instead of just my own?

Thanks!
Pieter


Here you go: http://sqlsaturday.com/235/schedule.aspx

Actually you can view all SQL Saturday events that have ever occurred (over 200 since 2007) here: http://sqlsaturday.com/events.aspx. open up each schedule page and anything you see that interests you that has an orange Download button is freely-available content. I feel it is one of the best SQL Server learning resources on the web. I am a bit biased though since I have presented at almost 60 of the events! Cool

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
pietlinden
pietlinden
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5194 Visits: 13213
SQL Saturday is awesome! I saw Denny Cherry (covering for someone else) do a super basic presentation with his hands in his pockets the whole time. Saw Rick Morelan too...

Absolutely worth going to, even if you're a relative noob, like me!
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