Обсуждение: Help with Outer Joins

I come from an Oracle background and have noted that postgres 7.1 supports outer joins...but I'm not sure of the
syntax. Note below in the first AND clause the (+) next to k.permit_id, I need to get the nulls back as well as the
valuebut I get an error when I use (+) 

SELECT t.permit_id, t.issue_date, t.issued_by, t.location, t.purpose ||' '|| t.subpurpose as spurpose, t.date_from,
t.date_to,t.permit_conditions, t.other_info, k.key_code, p.person_id, p.firstname ||' '|| p.lastname as name 
FROM person p, forest_permit t, permit_key k
WHERE p.person_id = t.person_id
AND t.permit_id = k.permit_id(+)
AND p.lastname LIKE 'Bloggs'
AND p.firstname LIKE 'Joe'
ORDER BY t.issue_date


Best Regards,

Sharon Cowling

Sharon,

> I come from an Oracle background and have noted that postgres 7.1
> supports outer joins...but I'm not sure of the syntax.  Note below in
> the first AND clause the (+) next to k.permit_id, I need to get the
> nulls back as well as the value but I get an error when I use (+)

The (+) method of performing outer joins is an Oracle proprietary
deviation from the SQL92 standard.   No other database uses this.

The PostgreSQL, and SQL92 standard, method for performing outer joins is
to use and explicit join syntax:

SELECT *
FROM table_a LEFT OUTER JOIN table_b
  ON table_a.1 = table_b.2

Please be careful using LEFT and RIGHT outer joins to get the joined
tables on the correct sides!

See:
http://www.postgresql.org/idocs/index.php?sql-select.html
for more information on JOIN syntax.

see:
http://techdocs.postgresql.org/
for articles on porting from Oracle to Postgres

-Josh



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco