Обсуждение: Outer Join Syntax

I'm doing a feasability study on porting our flagship product to Postgres
(from MS_SQL).  I have run across a few snags, the largest of which is the
outer join syntax.  MS has some nice syntactical sugar with the *=/=*
operators that Postgres dosen't seem to support.  I am confused on how
to replicate the behavior however. We often link together many tables via
outer joins to form a view such as:
SELECT Assessment_medical_id, a.Readonly_agency, a.Date_added, ag.name as
'AgencyName',              y1.Yesno_descrip as 'healthprob', y2.Yesno_descrip as
'MentalIllness', y3.Yesno_descrip as 'MentalTreatment',              y4.Yesno_descrip as 'drugabuse',
d1.Drug_abuse_type_descrip
as 'drug1', d2.Drug_abuse_type_descrip as 'drug2',              d3.Drug_abuse_type_descrip as 'drug3',
d4.Drug_abuse_type_descrip as 'drug4', d5.Drug_abuse_type_descrip as
'drug5'              FROM ASSESSMENT_MEDICAL a, AGENCIES ag, YESNO_TYPES02 y1,
YESNO_TYPES02 y2, YESNO_TYPES02 y3, YESNO_TYPES02 y4,              DRUG_ABUSE_TYPES d1, DRUG_ABUSE_TYPES d2,
DRUG_ABUSE_TYPES
d3, DRUG_ABUSE_TYPES d4, DRUG_ABUSE_TYPES d5              WHERE a.inactive != 'Y'              AND a.Client_id =
$Edit_Client_id             AND a.Agency_creating *= ag.Agency_id              AND a.Health_prob *= y1.Yesno_code
      AND a.EmoMental_illness *= y2.Yesno_code              AND a.Treatment_for_emomental *= y3.Yesno_code
ANDa.AlchoholDrug_abuse *= y4.Yesno_code              AND a.AlchoholDrug_abuse_type1 *= d1.Drug_abuse_type_id
  AND a.AlchoholDrug_abuse_type2 *= d2.Drug_abuse_type_id              AND a.AlchoholDrug_abuse_type3 *=
d3.Drug_abuse_type_id             AND a.AlchoholDrug_abuse_type4 *= d4.Drug_abuse_type_id              AND
a.AlchoholDrug_abuse_type5*= d5.Drug_abuse_type_id
 


I'm just not grasping how one would accomplish the same using the SQL-92
syntax.

TIA

Subject: [SQL] Outer Join Syntax


> I'm doing a feasability study on porting our flagship product to Postgres
> (from MS_SQL).  I have run across a few snags, the largest of which is the
> outer join syntax.  MS has some nice syntactical sugar with the *=/=*
> operators that Postgres dosen't seem to support.  I am confused on how
> to replicate the behavior however. We often link together many tables via

See http://www.postgresql.org/idocs/index.php?queries.html

You also might want to take a look at
http://www.postgresql.org/idocs/index.php?explicit-joins.html

HTH,

--Joe

"Richard Rowell" <richard@bowmansystems.com> 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 aLEFT JOIN AGENCIES ag ON a.Agency_creating = ag.Agency_idLEFT JOIN YESNO_TYPES02 y1 ON
a.Health_prob= y1.Yesno_codeLEFT 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