Get me the clear picture of outer joins in Oracle 9i

Question

Outer joins seem to me a little bit confusing.

Is there anyone can get me a clear picture of outer joins (right, left and full)?

Answer 1

Orginal question/answer: https://stackoverflow.com/questions/3308122/how-do-i-decide-when-to-use-right-join-left-join-or-inner-join-or-how-to-deter/3308153#3308153

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

Answer 2

The Wikipedia article about Joins should help you.

The special thing about Oracle Outer joins was the old notation using (+), but it is obsolete now.

Answer 3

The way I always think of it is:

LEFT JOIN - keeps all rows selected on the left  
RIGHT JOIN - keeps all rows selected on the right  
FULL JOIN - keeps all rows selected on both sides  

Easy to keep in your head, without going to Wikipedia or drawing diagrams.

P.S. Death to Oracle + join syntax!

Answer 4

I've never liked those oft seen pictures used in @Pranay Rana's answer. They show set operations but not joins. For example, the top middle picture is A union B, the left middle is A except B (or I suppose A minus B for Oracle), and so on.

Also consider that null does not exist in the relational model, so you can't draw a picture of it using a Venn diagrams anyway! ;)

I don't think one can draw a picture of an outer join without using tables, such as this one.