Oracle Joins

The Oracle outer join changed between versions 8i and 9i:

8i

SELECT p.name, o.name FROM person p, organisation o WHERE p.organisation_id = o.id (+)

9i

SELECT p.name, o.name FROM person p LEFT OUTER JOIN organisation o ON p.organisation_id = o.id

Multiple Outer Joins

It is possible to do an outer join on more that one table. Example: SELECT O.ID, O.NAME A.ADDRESS AS ADDRESS, A.CITY, A.POSTCODE, COUNTRY.NAME AS COUNTRY, COUNTY.NAME AS COUNTY, A.POSTCODE AS POSTCODE, FROM ORGANISATION O, ADDRESS A, LOOKUP COUNTRY, LOOKUP COUNTY WHERE O.ID = A.ORGANISATIONID (+) AND A.COUNTRYID = COUNTRY.ID (+) AND A.COUNTYID = COUNTY.ID (+) AND O.ID=33

Andrew McDonough is a consultant CTO and software developer, currently based between Berlin and London.

Follow Andrew on Twitter or Connect on LinkedIn