At 10:34 AM 9/29/99 -0400, Tom Lane wrote:
>play=> select x.f1 from x, x as x2;
>f1
>--
> 1
> 2
> 3
>(3 rows)
>
>It seems to me that the latter query must yield 9 rows (three
>occurrences of each value) to satisfy the SQL spec. The spec defines
>the result of a two-query FROM clause to be the Cartesian product of the
>two tables, period. It doesn't say anything about "only if one or more
>columns of each table are actually used somewhere".
AFAIK, this is correct. For the heck of it, I tried it in
Oracle, and indeed the full cartesian product's returned:
SQL> select x2.i from x, x x2;
I
---------- 1 1 1 2 2 2 3 3 3
9 rows selected.
>play=> select count(1) from x;
>count
>-----
> 1
>(1 row)
Again, Oracle 8:
SQL> select count(1) from x, x x2;
COUNT(1)
---------- 9
SQL>
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.