ORACLE 各種連接

tingx 2016-10-08

展開全文

數(shù)據(jù)說明：

select * from dave;
ID　 NAME
8    安慶
1    dave
2    bl
1    bl
2    dave
3    dba
4    sf-express
5    dmm

select * from b1;
ID　　NAME
1    dave
2    bl
3    big bird
4    exc
9    懷寧

1. inner join(join)

　　　join默認為inner join.

以下3種寫法的效果是一致的：

select * from dave a inner join b1 b on a.id = b.id;
select * from dave a join b1 b on a.id = b.id;
select * from dave a, b1 b where a.id = b.id;

ID　　NAME　　　　　 ID　 NAME
1    dave          1    dave
2    bl            2    b1
1    bl            1    dave
2    dave          2    b1
3    dba           3    big bird
4    sf-express    4    exc

自然連接natural join

尋找數(shù)據(jù)類型和列名相同的字段，自動將他們連接起來，并返回所有符合條件的結(jié)果。

下面兩種寫法的效果是一致的：

select * from dave a natural join b1 b;
select a.id, b.name from dave a join b1 b on a.id = b.id and a.name = b.name;

ID　　NAME
1    dave
2    bl

注意：1. 如果自然連接的兩個表有多個字段滿足相同名稱和類型，則會作為自然鏈接的條件

　　　2. 如果數(shù)據(jù)類型不同，則會報錯。

2. Outer join

LEFT JOIN /RIGHT JOIN/FULL JOIN. 通常省略OUTER。

（+）也可以用來表示外連接。

（一）+操作符只能在where子句中，不能與outer join同時使用。

（二）+操作符只能執(zhí)行外連接，如果在where中包含多個條件，則所有條件都包含+操作符。

（三）+操作符只適用于列，而不能用在表達式上。

（四）+操作符不能與or in操作符一起使用。

（五）操作符只能用于實現(xiàn)左外連接和右外連接，不能用于全外連接。

2.1 左外連接left join +

+ 表示補充，哪個個表有加號，這個表就是匹配表。所以加號寫在右表，左表就是全部顯示，故是左連接。

以下三種寫法的效果是一致的：

select * from dave a left join b1 b on a.id = b.id;
select * from dave a left outer join b1 b on a.id = b.id;
select * from dave a ,b1 b where a.id = b.id(+);

ID　　NAME　　　　　ID　　NAME
1    bl   　　 　　 1    dave
1    dave     　　  1    dave
3    dba    　　　　3    big bird
4    sf-express　　 4    exc
2    dave    　　　 2    bl
2    bl    　　　　 2    bl
5    dmm        　　--沒有匹配到，為null
8    安慶        　--沒有匹配到，為null

2.2 右外連接right join +

以下三種寫法的效果是一致的：

select * from dave a right join b1 b on a.id = b.id;
select * from dave a right outer join b1 b on a.id = b.id;
select * from dave a ,b1 b where a.id(+) = b.id;

ID   NAME　　　　　　ID   NAME
1    dave    　　　　1    dave
2    bl    　　　　　 2    bl
1    bl    　　　　　 1    dave
2    dave   　　　　 2　　    bl
3    dba    　　　　 3    big bird
4    sf-express     4    exc
9    懷寧　　　　　　　　--沒有匹配到，為null

2.3 全外連接（full outer join/full join）

左表和右表都不做限制，所有的記錄都顯示，兩表不足的地方用null填充。全外連接不支持（+）這種寫法。

select * from dave a full join b1 b on a.id = b.id;

ID   NAME 　　　ID  NAME
8    安慶        　　--NULL
1    dave  　　　　　　1    dave
2    bl　　　　　　　　 2    bl
1    bl　　　　　　　　 1    dave
2    dave　　　　　　  2    bl
3    dba    　　　　  3    big bird
4    sf-express      4    exc
5    dmm             --NULL
--NULL       　　　　　9    懷寧