Join__oracle in Oracle

L Transfer from: http://www.cnblogs.com/lovemoon714/archive/2012/03/02/2376782.html

1. Overview

1.1, all join connection, can add like where a.id= ' 1000 ' condition, achieve the same effect.

1.2, in addition to the cross join can not add on, other join joins must be added on the keyword, after all can add where conditions.

1.3, although all can add where conditions, but they only on the standard connection result set to find where conditions. For example, the result of the left outer connection does not have class three classes, so if add where class.id= ' C003 ' although in the table has, but in the left connection result set does not have, therefore the query, is not recorded.

2, instance, the standard join connection, (without the Where condition)

2.1. The following table is provided:

Student table

Class table, corresponding to the ClassID in the student table

2.2, from the connection: join, INNER join

1--Self-connection  : Returns only matches for two table join columns.
2-The following three kinds of query results are the same.
3 SELECT * FROM student s inner join Class C on s.classid=c.id; 
4 SELECT * FROM student S join Class C on s.classid=c.id;
5 SELECT * FROM student S,class C where s.classid=c.id;

Self-connection result:

2.3, the flute Descartes product: Cross join

1--Flute Descartes Product Connection: That is, without any conditions, to achieve m*n result set.
2-The following two kinds of query results are the same.
3 SELECT * FROM Student S Cross join Class C;
4 SELECT * from Student,class;

Descartes Results:

Note: If cross join adds a where s.classid=c.id condition, it produces the same result as a connection:

1--plus conditions, resulting in the same result as the self connection.
2 Select * from student S Cross join Class C where s.classid=c.id;

Cross join connection results from a connection result set

2.3. Left OUTER join:

1--Left JOIN connection: List all of the left table, and the right table to meet the conditions, do not meet the conditions of NULL value instead.
2-in (+) calculation, which band (+) which need conditions, the other all. Left-right connection, right or left.
3--The following result sets are the same.
4 SELECT * FROM student s left join Class C on s.classid=c.id;
5 SELECT * FROM student S,class C where s.classid=c.id (+);

Left JOIN results:

2.4. Right outer connection: right-hand join

1--Right outer connection: the same as left join, listing all of the right table, and the table on the left to meet the conditions, does not conform to the conditions of
2--The use of NULL value  substitution.
3-(+), its position is the opposite of the connection.
4 SELECT * FROM student s Right join Class C on s.classid=c.id;
5 SELECT * FROM student S,class C where S.CLASSID (+) =c.id;

Right connection result

2.5, full join:

1-Full connection: produces the m+n result set, lists the two tables all, does not meet the condition, takes the null value to replace.
2 Select * FROM student s full join Class C on s.classid=c.id;

Full join result set