Correlated SQL-Derived Tables Are Not Supported

Transact-SQL does not support correlated SQL-derived tables, which are also not ANSI standard.

For example, the following query is not supported because it references the SQL-derived table dt_publishers2 inside the derived-table expression for dt_publishers1:

select * from 
   (select * from titles where titles.pub_id =
      dt_publishers2.pub_id) dt_publishers1,
   (select * from publishers where city = "Boston")
      dt_publishers2
where dt_publishers1.pub_id = dt_publishers2.pub_id

Similarly, the following query is not supported because the derived-table expression for dt_publishers references the publishers_pub_id column, which is outside the scope of the SQL-derived table:

select * from publishers
  where pub_id in (select pub_id from 
                      (select pub_id from titles
                      where pub_id = publishers.pub_id)

                   dt_publishers)

The following query illustrates proper referencing and is supported:

select * from publishers
  where pub_id in (select pub_id from
                      (select pub_id from titles)
                   dt_publishers
                     where pub_id = publishers.pub_id)