Joe Celko's SQL for Smarties, 3rd Edition

17.4. Scope of Derived Table Names

Another problem is that many SQL programmers do not fully understand the rules for the scope of names. If an infixed join is given a derived table name, then all of the table names inside it are hidden from containing expressions. For example, this will fail:

SELECT a, b, c -- wrong!
 FROM (Foo
       INNER JOIN
       Bar ON Foo.y >= Bar.x) AS Foobar (x, y)
      INNER JOIN
      Flub ON Foo.y <= Flub.z;

It fails because the table name Foo is not available to the second INNER JOIN. However, this will work:

SELECT a, b, c
 FROM (Foo
       INNER JOIN
       Bar ON Foo.y >= Bar.x) AS Foobar (x, y)
      INNER JOIN
      Flub ON Foobar.y <= Flub.z;

If you start nesting lots of derived table expressions, you can force an order of execution in the query. It is generally ...