Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select in select subquery Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 11:59 AM
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?


Post #1489232
Posted Wednesday, August 28, 2013 8:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1489251
Posted Wednesday, August 28, 2013 12:53 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:45 PM
Points: 4,128, Visits: 5,837
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 at GMail
Post #1489382
Posted Wednesday, August 28, 2013 9:06 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:20 AM
Points: 578, Visits: 3,755
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
Post #1489503
Posted Thursday, August 29, 2013 1:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1489557
Posted Thursday, August 29, 2013 7:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:45 PM
Points: 4,128, Visits: 5,837
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 at GMail
Post #1489728
Posted Friday, August 30, 2013 8:33 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:20 AM
Points: 578, Visits: 3,755
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!
Post #1490342
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse