Using same alias in two or more DIFFERENT correlated subqueries (1 Viewer)

[Isaac]

Say I have a Select list with some correlated subqueries, like:

OutputColumnAlias1 = (select min(somedate) from Table1 t1 where t1.patientid=OuterTableAlias.patientid and t1.labname='labname1')
OutputColumnAlias2 = (select min(somedate) from Table1 t1 where t1.patientid=OuterTableAlias.patientid and t1.labname='labname2')

Is it acceptable to repeat t1 in the second subquery, because it's "scope" will only be limited to the inside of that parenthetical?

Or am I unwittingly producing a bad result because, due to the first subquery, t1 will forever be defined by it's first referenced instance's WHERE clause, (etc) ?

Setting aside the badness of correlated subqueries for a moment, please advise?

PLEASE NOTE THIS IS NOT MICROSOFT ACCESS, THANKS!

 

[Isaac]

PS I think I proved to myself that "yes that was the wrong thing to do",
as I changed the 2nd correlated subquery's alias to be unique and it solved the problem.

Gosh, what a beginner's mistake, I feel a bit badly about it. Fortunately, I can say "I didn't know that because I don't usually have a lot of those correlated subqueries involved!"

 

[The_Doc_Man]

I'm glad you solved your problem, but if you are operating in ANSI standard SQL 92, the aliases in the query should not persist across multiple queries. I looked through the ANSI/ISO SQL1999 document and it does not appear to allow persistence of aliases. (Also called "correlation names" if you wanted to search for it.)

 

[Isaac]

That's interesting Doc. I'll confess, I've never become familiar with or really delved into those documents, although I have a deep appreciation for the concept of ansi standards. So, I'll take your word for it. I just wonder if subqueries (in the context I originally posted), are an exact match for the "across multiple queries" - I mean taking it a literal word value, Yes, I agree it would seem so.

If that's the case, I'd be perhaps a bit disappointed in SQL Server for allowing its T-SQL wrapper to cause a deviation of such fundamental aspect from ANSI standard. Not that it never deviates from the ANSI but I've always liked to believe that it mostly does "adding on", rather than "taking away" functionality.

Glad I learned this now, it will save me from re-doing a few tickets down the road....

 

[The_Doc_Man]

From ISO/IEC 9075-2:1999 (E), which is the ANSI/ISO/IEC International Standard (IS), Database Language SQL—Part 2: Foundation (SQL/Foundation), section 5.4 Names and identifiers, sub-paragraph 16, I have copied their comments and added an underline to the part that I believe is relevant to your question. In this particular document, anything that they specifically named in some Backus-Naur style of definition is shown in angle-brackets <> to highlight it, so don't let the bracketing fool you. It just means it's a term you could look up in their glossary.

An <identifier> that is a <correlation name> is associated with a table within a particular scope.
The scope of a <correlation name> is either a <select statement: single row>, <subquery>, or
<query specification> (see Subclause 7.6, ‘‘<table reference>’’), or is a <trigger definition> (see
Subclause 11.38, ‘‘<trigger definition>’’). Scopes may be nested. In different scopes, the same
<correlation name> may be associated with different tables or with the same table.

When dealing with a sub-query, you have a nested scope (statement within a statement) which is why they mention nesting. Triggers and queries are independent entities in their definition and each are considered as self-contained. But there is no overarching scope greater than a single complete query with any and all of its included sub-queries. Therefore, from one complete SQL query to the next, I would say that any SQL implementation adhering to the published standard would treat correlation names/aliases in an SQL statement as independent of the correlation names/aliases in another SQL statement.

 

[Isaac]

Therefore, from one complete SQL query to the next, I would say that any SQL implementation adhering to the published standard would treat correlation names/aliases in an SQL statement as independent

Well I suppose the way they did it make sense then. Because these were different subqueries in a select statement belonging to one complete query. Perhaps I shouldn't have expected the scope to be so intensely limited.