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


T-SQL correlated queries


T-SQL correlated queries

Author
Message
Evgeny Garaev
Evgeny Garaev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5943 Visits: 1594
Comments posted to this topic are about the item T-SQL correlated queries
Toby Harman
Toby Harman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 758
I had to cheat to get this and actually run the code. I got suspicious when I couldn't see the answer I was expecting "Invalid column name 'c1'."
Personally I think that's a bug, because the inner select returns no rows and should therefore be equivalent to

select c1
from dbo.t1
where c1 in ()

which returns a syntax error (but I can also understand an empty set as there's nothing inside the IN statement).

Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69874 Visits: 8344
Toby Harman - Wednesday, March 13, 2019 10:40 PM
I had to cheat to get this and actually run the code. I got suspicious when I couldn't see the answer I was expecting "Invalid column name 'c1'."
Personally I think that's a bug, because the inner select returns no rows and should therefore be equivalent to

select c1
from dbo.t1
where c1 in ()

which returns a syntax error (but I can also understand an empty set as there's nothing inside the IN statement).

Nice question, thanks Evgeny.

Tony, this is not a bug, but a side effect of correlated subqueries.
all columns from the main SELECT are available for use in the subselect, so it will compile.
per this extract from Microsoft docs:
Important

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Toby Harman
Toby Harman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 758
Thanks Stuart
So the subquery becomes (SELECT t1.c1 from t2) which makes even less sense to me!
I appreciate that t1 is in scope and therefore can be parsed and used, and that MS have documented this behaviour, but I doubt many would find it desirable!
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)SSC Guru (182K reputation)

Group: General Forum Members
Points: 182687 Visits: 22098
Toby Harman - Thursday, March 14, 2019 12:03 AM
Thanks Stuart
So the subquery becomes (SELECT t1.c1 from t2) which makes even less sense to me!
I appreciate that t1 is in scope and therefore can be parsed and used, and that MS have documented this behaviour, but I doubt many would find it desirable!


Toby it might make more sense if you were doing something like this:
(SELECT t1.c1 * t2.c1 FROM 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
alex.schievink 85730
alex.schievink 85730
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 34
Nice example to demonstrate that you should name your tables and columns well, to avoid confusion.
Jonathan AC Roberts
Jonathan AC Roberts
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15414 Visits: 6388
Another reason why you should always prefix the column names with the table name (or alias) .
srienstr
srienstr
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1996 Visits: 590
Jonathan AC Roberts - Thursday, March 14, 2019 5:48 AM
Another reason why you should always prefix the column names with the table name (or alias) .

Indeed!
select t1.c1
from dbo.t1
where t1.c1 in (select t2.c1 from t2)

That would give the expected error.
I'll admit: if I had seen the error I expected in the options, I might not have looked at the code closely enough to get it right.



Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
webrunner
webrunner
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29043 Visits: 4532
Thanks for this instructive question!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
n.ryan
n.ryan
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: 2124 Visits: 438
Jonathan AC Roberts - Thursday, March 14, 2019 5:48 AM
Another reason why you should always prefix the column names with the table name (or alias) .


Definitely. Similar to why I always put brackets around logic statements to make it very clear the order of precedence otherwise one small change elsewhere can break an entire query...
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