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 ...
Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.