When you want to see a list of all values from two tables in a join, even if there are no matches, you want the Full Outer join. It fills in nulls on both sides where needed.

Try this query:

SELECT *
 FROM hr.regions FULL OUTER JOIN hr.countries USING (region_id);

If you look through the data, you will see that all regions have countries and all countries have regions.

Let’s add a couple unmatched values.

INSERT INTO hr.countries VALUES ('NZ','New Zealand',NULL);

INSERT INTO hr.regions VALUES (5,'Antarctica');

Run the same query again. New Zealand shows up without a region. Antarctica has no countries. Nothing is left out.

Another technique would be to union two queries, one with a LEFT OUTER JOIN, the other with a RIGHT OUTER JOIN. The Union removes all duplicates and you are left with all data including null fill-ins where data is absent.

SELECT *
 FROM hr.regions r, hr.countries c
 WHERE r.region_id(+) = c.region_id
UNION
SELECT *
 FROM hr.regions r, hr.countries c
 WHERE r.region_id = c.region_id(+)
Tagged: