Home Forums Article Discussions Article Discussions by Author Discuss content posted by Evgeny Garaev T-SQL correlated queries RE: T-SQL correlated queries

  • March 13, 2019 at 11:44 pm

    #2024413

    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”