Thanks Hugo for your clarification and thanks Richard for the link.

I do use correlated subqueries in a daily basis but in my mind, the outer table can only be referenced in the WHERE clause in the subquery, not in the SELECT list of the subquery.

1) I checked BOL about subquery, it has a Caution section in the end:

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

So I can't blame MS. It does a good job to document what SQL Server does in this scenario.

2) I also checked Oracle (10g for Windows, express edition), its behaviour is the same as MS SQL Server.

So two major RDBMS are handling this in the same way. I am not sure this is defined by SQL-99/2003 spec or not.

My apologize to everyone if the article caused any confusion. However, frankly i am not fully convinced. The subquery that select a column from the outer table is cumbersome and confusing in terms of semantics. It always has more concise and clear way for this type of queries. To me, it brings in more troubles than a feature. And I guess that's why MS put a caution section in the BOL.

I haven't used and will not use this type of correlated subqueries. To avoid the typos or mistakes, I'll follow Hugo's suggestion to scope the referenced columns.