This is an example showing the difference between left, right and full outer joins that I found on orafaq.com, so all credit to Barbara Boehmer! Babs says:
A FULL OUTER JOIN is the combined results of a LEFT OUT JOIN and RIGHT OUTER JOIN. I have provided an example of LEFT OUTER JOIN, RIGHT OUTER JOIN, then FULL OUTER JOIN below. I have provided both the 9i ANSI SQL syntax and 8i syntax, which is still usable in 9i, but I have only run the 8i syntax, since I am using 8i. Also, the OUTER keyword is optional, so, for example, LEFT OUTER JOIN is the same as LEFT JOIN.
SQL< -- test data: SQL< SELECT * FROM a 2 / ID COL2 ---------- ------------------------- 1 id value in both tables 2 id value in table a only SQL< SELECT * FROM b 2 / ID COL2 ---------- ------------------------- 1 id value in both tables 3 id value in table b only SQL< -- left outer join with 9i syntax: SQL< -- SELECT a.id, a.col2, b.id, b.col2 SQL< -- FROM a LEFT OUTER JOIN b ON a.id = b.id SQL< -- / SQL< -- left outer join with 8i syntax: SQL< SELECT a.id, a.col2, b.id, b.col2 2 FROM a, b 3 WHERE a.id = b.id (+) 4 / ID COL2 ID COL2 ---------- ------------------------- ---------- ------------------------- 1 id value in both tables 1 id value in both tables 2 id value in table a only SQL< -- right outer join with 9i syntax: SQL< -- SELECT a.id, a.col2, b.id, b.col2 SQL< -- FROM a RIGHT OUTER JOIN b ON a.id = b.id SQL< -- / SQL< -- right outer join with 8i syntax: SQL< SELECT a.id, a.col2, b.id, b.col2 2 FROM a, b 3 WHERE a.id (+) = b.id 4 / ID COL2 ID COL2 ---------- ------------------------- ---------- ------------------------- 1 id value in both tables 1 id value in both tables 3 id value in table b only SQL< -- full outer join with 9i syntax: SQL< -- SELECT a.id, a.col2, b.id, b.col2 SQL< -- FROM a FULL OUTER JOIN b ON a.id = b.id SQL< -- / SQL< -- full outer join with 8i syntax: SQL< SELECT a.id, a.col2, b.id, b.col2 2 FROM a, b 3 WHERE a.id = b.id (+) 4 UNION ALL 5 SELECT a.id, a.col2, b.id, b.col2 6 FROM a, b 7 WHERE a.id (+) = b.id 8 AND a.id IS NULL 9 / ID COL2 ID COL2 ---------- ------------------------- ---------- ------------------------- 1 id value in both tables 1 id value in both tables 2 id value in table a only 3 id value in table b only