Oracle 內外連線 join 總結

join其實就是個連結兩個表 的一種粘合劑 ，不同點只是這個粘合劑 的構成成分不同 罷了。
一、join的用法如下:
test1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN test2 ON boolean_expression
test1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN test2 USING ( join column list )
test1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN test2
注:
1、在inner和outer中要二選一 ,不過inner 是預設 的(就是啥都不寫的話,就預設inner )。
當然如果寫上left、right、full這些的話，前面那個預設 就不是inner 啦，而變成outer 了，
誰叫人家outer 有保鏢 (left、right、full)呢。。。
2、連線條件 在on 或using 子句裡宣告， 或者用關鍵字natual L隱含 地宣告．連線條件判斷來自兩個源表中的哪
些行是"匹配"的。構成成分不同
其中:
(1)on 子句是最常見的連線條件的型別∶它接收一個和WHERE 子句裡用的一樣(不過沒有where啊) 的布林值表示式 ．如果兩個分別來自test1和 test2 的行在on 表示式上運算的結果為真，那麼它們就算是匹配的行。
(2)using 是縮寫的概念∶它接收一個用逗號分隔的欄位名字列表， 這些欄位必須是連線表共有的，最終形成一個連線條件，表示這些欄位對必須相 同．最後，JOIN USING 的輸出會為每一對相等 的輸入欄位輸出一個欄位，後面跟著來自各個表的所有其它欄位．因此，USING (a, b, c) 等效於 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 。 只不過是如果使用了ON 時結果裡 a，b，和 c欄位每個都會有兩個 ，而用USING 的時候每個欄位就只會有一個 (去除相同的列 )。
3、natual 是using 的縮寫形式(即連線的條件沒有被寫出來 )：它形成一個 using 列表， 該列表由那些在兩個表裡都出現了的欄位名字組成．和USING一樣， 這些欄位只在輸出表裡出現一次．
二、join的可能型別是∶
INNER JOIN（內連線）
對於 test1 的每一行 R1，生成的連線表都有一行對應 test2 中的 每一個滿足和 R1 的連線條件的行．
簡述: 用一個條件連線兩個表
LEFT OUTER JOIN（左外連線）
首先，執行一次內連線．然後，為 test1 裡那些和 test2 裡任何一行都不滿足連線條件的行返回一個連線行， 同時該連線行裡對應 test2 的列用空值補齊．因此， 生成的連線表裡無條件地包含來自test1 裡的每一行至少 一個副本．
簡述: inner join + join左邊表 裡面沒出現的內容
RIGHT OUTER JOIN
首先，執行一次內連線．然後，為 test2 裡那些和 test1 裡任何一行都不滿足連線條件的行返回一個連線行， 同時該連線行裡對應 test1 的列用空值補齊．因此， 生成的連線表裡無條件地包含來自 test2 裡的每一行．
簡述: inner join + join右邊表 裡面沒出現的內容
FULL OUTER JOIN
首先，執行一次內連線．然後，為test1 裡那些和test2 裡任何一行都不滿足連線條件的行返回一個連線行， 同時該連線行裡對應 test2 的列用空值補齊． 同樣，為test2 裡那些和 test1 裡的任何行都不滿足連線條件的 行返回一個連線行，該行裡對應 test1 的列用空值補齊．
簡述: inner join + join左右邊表 裡面沒出現的內容
如果test1 和 test2 有一個或者都是可以連線(join)的表， 那麼所有型別的連線都可以串在一起或巢狀在一起． 你可以在JOIN子句周圍使用圓括弧來控制連線順序， 如果沒有圓括弧，那麼JOIN子句是從左向右巢狀的．
三、建立兩個表來詳細的理解理解把！！！
test1表
create table test1(id number,name varchar2(10));
insert into test1 values(1,'gg1');
insert into test1 values(2,'gg2');
insert into test1 values(3,'gg3');
test2表
create table test2(id number,course varchar2(10));
insert into test2 values(1,'qq1');
insert into test2 values(2,'qq2');
insert into test2 values(6,'qq6');
1、cross join（相當於做了笛卡爾積）
select * from test1 cross join test2;
ID NAME ID COURSE
---------- ---------- ---------- ----------
1 gg1 1 qq1
1 gg1 2 qq2
1 gg1 6 qq6
2 gg2 1 qq1
2 gg2 2 qq2
2 gg2 6 qq6
3 gg3 1 qq1
3 gg3 2 qq2
3 gg3 6 qq6
2、inner join
select * from test1 t1 join test2 t2 on t1.id=t2.id; 或 select * from test1 t1 inner join test2 t2 on t1.id=t2.id;
ID NAME ID COURSE
---------- ---------- ---------- ----------
1 gg1 1 qq1
2 gg2 2 qq2
3、natural (inner) join （只能 有一個相同 的列名 作為橋接,如果有兩個的話就出不了結果啦！！切記。。）
select * from test1 natural inner join test2; 或 select * from test1 natural join test2;
ID NAME COURSE
---------- ---------- ----------
1 gg1 qq1
2 gg2 qq2
4、left join（左外連線）
select * from test1 t1 left join test2 t2 on t1.id=t2.id ; 或 select * from test1 t1 left outer join test2 t2 on t1.id=t2.id ;
ID NAME ID COURSE
---------- ---------- ---------- ----------
1 gg1 1 qq1
2 gg2 2 qq2
3 gg3
5、right join
select * from test1 t1 right join test2 t2 on t1.id=t2.id ; 或 select * from test1 t1 right outer join test2 t2 on t1.id=t2.id ;
ID NAME ID COURSE
---------- ---------- ---------- ----------
1 gg1 1 qq1
2 gg2 2 qq2
6 qq6
6、full join
select * from test1 t1 full join test2 t2 on t1.id=t2.id; 或 select * from test1 t1 full outer join test2 t2 on t1.id=t2.id;
ID NAME ID COURSE
---------- ---------- ---------- ----------
1 gg1 1 qq1
2 gg2 2 qq2
3 gg3
6 qq6
7、left/right/full join 中應用 using,來對比using (natural) 和on 的區別
select * from test1 left join test2 using(id);
ID NAME COURSE
---------- ---------- ----------
1 gg1 qq1
2 gg2 qq2
3 gg3
select * from test1 t1 left join test2 t2 on t1.id=t2.id;
ID NAME ID COURSE
---------- ---------- ---------- ----------
1 gg1 1 qq1
2 gg2 2 qq2
3 gg3
看到區別了吧，區別就是ID有沒有被合併！！！
8、and
這個and 嗎，就是在on後面還可以新增約束條件的，例如:
select * from test1 t1 join test2 t2 on t1.id=t2.id and t1.name='gg1';
ID NAME ID COURSE
---------- ---------- ---------- ----------
1 gg1 1 qq1
9、這個主要是總結一下
其實上面8點 (是SQL:1999 標準)完全可以用普通的SQL語句 替換，而且結果是一樣的！！！只是那樣會更直觀、好理解......
下面我來舉一個小例子,來替換上面的左外連線
select * from test1 t1 , test2 t2 where t1.id=t2.id(+);
ID NAME ID COURSE
---------- ---------- ---------- ----------
1 gg1 1 qq1
2 gg2 2 qq2
3 gg3
是不是和上面 left join 的結果一樣。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25897606/viewspace-712514/，如需轉載，請註明出處，否則將追究法律責任。