Posted by --CELKO-- on 10/24/07 20:46

>> But why/how is colX "available" in both tables? <<

colx is available to **the subquery** and not in both table at all.
Queries first try to reference the nearest column, and move outward in
the scope of query nesting.

>> Maybe there are some basics that I don't understand... <<

Scoping rules in SQL are similar to the scoping rules in block
structure programming languages like Algol, C, PL/I, ADA, etc.

A: BEGIN
x INTEGER;
y INTEGER;
..
B: BEGIN
x INTEGER;
x := x + y;
..
END;
END;

When you get to block B, the local x is referenced in that assignment
statement, but the y in the containing block A is referenced.


But SQL is a bit more complicated. If two query expression are on the
same level, then you have to use a LATERAL operator in Standard SQL.
When a query expression is given a name and is it called a derived
table. Only that derived table is exposed to higher nesting level.

I have a chapter in THINKING IN SETS on this; the book is due out in
February 2008.

[Back to original message]