Given the two tables COUNTRIES and LOCATIONS, in a 1-> m relationship, with a foreign key in the LOCATIONS table.
Not all the countries have a location child record.
These two statements illustrate the use of a left outer join:
1. Display all the records of LOCATIONS, and the related records in COUNTRIES:
select c.country_name, loc.*
from locations loc
left outer join countries c
on c.country_id = loc.country_id;
The result is equivalent (in this case) to a inner join, since no record in LOCATIONS have no parent in COUNTRIES.
2. Display all the records of COUNTRIES, and the related records in LOCATIONS (if any):
select c.country_name, loc.city
from countries c
left outer join locations loc
on c.country_id = loc.country_id
order by c.country_name;
COUNTRY_NAME NVL(LOC.CITY,'NULL')
------------------------------------ ------------------------------
Argentina NULL
Australia Sydney
Belgium NULL
Brazil Sao Paulo
...
