Select in select subquery

Question

Post reply

Select in select subquery

bengosul

Grasshopper

Points: 11
More actions
August 28, 2013 at 7:54 am

#297274

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:
[font="System"]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)[/font]
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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

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 SSC Guru Points: 63428 More actions · Answer 3

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 SSC Guru Points: 186127 More actions · Answer 4

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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! 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

pietlinden SSC Guru Points: 63428 More actions · Answer 6

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!