Forum OpenACS Q&A: Response to Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data

Tom Lane (of PostgreSQL fame) posted to the pgsql-sql list (an excellent list btw) about this a while ago, when somebody referred to SQL Server/Oracle syntax as "nice syntatical sugar" and asked if PG would implement it or something like that. I'll paste a bit of his response here (favorite quote: "some of us view it as 'nonstandard and broken"), the entire post can be read at http://groups.google.com/groups?q=sugar+group:comp.databases.postgresql.sql&hl=en&selm=10530.996689163%40sss.pgh.pa.us&rnum=1.

> 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.
...