Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f71I6cf33849 for ; Wed, 1 Aug 2001 14:06:38 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f71I63C10533; Wed, 1 Aug 2001 14:06:03 -0400 (EDT) To: "Richard Rowell" cc: pgsql-sql@postgresql.org Subject: Re: Outer Join Syntax In-reply-to: <001f01c11abe$ebd29740$8301a8c0@RICHARD> References: <001f01c11abe$ebd29740$8301a8c0@RICHARD> Comments: In-reply-to "Richard Rowell" message dated "Wed, 01 Aug 2001 12:19:14 -0700" Date: Wed, 01 Aug 2001 14:06:03 -0400 Message-ID: <10530.996689163@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200108/11 X-Sequence-Number: 4232 "Richard Rowell" writes: > outer join syntax. MS has some nice syntactical sugar with the *=/=* > operators that Postgres dosen't seem to support. Some of us view it as "nonstandard and broken", not as "nice syntactical sugar" ;-). > I'm just not grasping how one would accomplish the same using the SQL-92 > syntax. SELECT ... FROM ASSESSMENT_MEDICAL a LEFT JOIN AGENCIES ag ON a.Agency_creating = ag.Agency_id LEFT JOIN YESNO_TYPES02 y1 ON a.Health_prob = y1.Yesno_code LEFT JOIN ... WHERE a.inactive != 'Y' AND a.Client_id = $Edit_Client_id; While this is more typing, it's clear which conditions determine joinability and which are filters on the overall result, which is a critical semantic issue that the Oracle/MS syntax fails miserably on. For example, suppose I want to add a constraint like y1.col3 = 'foo'. Now, if there are no rows meeting that constraint for a given value of a.Health_prob = y1.Yesno_code, does that mean I want to have ASSESSMENT_MEDICAL rows with that Health_prob show up with nulls substituted for the y1 fields? Or does it mean that I don't want to see those rows at all? AFAICS there's no way to make that distinction with the Oracle/MS approach. With the standard syntax, you put the additional constraint in ON in one case, and in WHERE in the other case. The standard syntax also allows you to control the join order by parenthesization, which is not so important for your star-query example, but is critical if you want to join two outer-join results together --- otherwise, you can't control which combinations result in partially-NULL rows out, and which result in no rows out. regards, tom lane