Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA60190 for ; Sat, 7 Aug 1999 10:06:05 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA11581; Sat, 7 Aug 1999 10:04:54 -0400 (EDT) To: Howie cc: pgsql-sql@hub.org Subject: Re: [SQL] table aliasing problem with 6.5... In-reply-to: Your message of Sat, 7 Aug 1999 13:06:57 +0000 (GMT) Date: Sat, 07 Aug 1999 10:04:53 -0400 Message-ID: <11578.934034693@sss.pgh.pa.us> From: Tom Lane Howie writes: > (1) why is it trying to do a cartesian join when not using the aliased > tables? That's what it's supposed to do. When you provide an alias for a table name in FROM, then as far as the rest of that query is concerned, that alias *is* the name of the table --- it has no other. When you refer to the original table name in the WHERE clause, that's taken as creating a separate table reference that's implicitly added to FROM. Your query is a four-way join with only one join having a restriction clause :-( The alias behavior is necessary in order to handle self-joins properly, for example to find married couples: SELECT * FROM person, person other WHERE person.spouse = other.spouse; This would be ambiguous if "person" were exposed by the second FROM clause. SQL92 requires it to work this way: ::=
[ [ AS ] [ ] ] ... 1) A immediately contained in a
TR is exposed by TR. A
immediately contained in a
TR is exposed by TR if and only if TR does not specify a . I think that implicitly adding a table to FROM is a Postgres extension not found in SQL92 --- we probably really ought to reject such a query with an error, since this behavior seems to be surprising... regards, tom lane