Search code examples
sql-servert-sqlsql-server-2016

TSQL Subquery Behavior

SQL Server docs don't indicate a requirement for using table aliases in subqueries, or when not used, the expected behavior. For example:

-- create and populate two tables with unique column names
create table tbl1 (col1 int);
insert tbl1 values (1);
create table tbl2 (col2 int);
insert tbl2 values (2);

-- execute query and subquery 
select col1 from tbl1 
    where col1 in (select col1 from tbl2);

The subquery alone returns the expected error:

Invalid column name 'col1'.

But the full query returns a result without raising an error.

query result

Is this behavior officially documented either by Microsoft or SQL-92 standard? Thanks!

Solution

  • Yes, the documentation now addresses this behavior:

    ”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.”