Get me the clear picture of outer joins in Oracle 9i
Outer joins seem to me a little bit confusing.
Is there anyone can开发者_C百科 get me a clear picture of outer joins (right, left and full)?
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.
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.
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!
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.
精彩评论