oracle 内外连接 join 总结

Oracle里这个 内外连接 让我可是晕晕...啊，不过现在总算弄明白啦！！！难道是本人天才.....
呵呵，开个玩笑，下面开始！！
等！！！！！还有个建议，我建议大家看第一、二部分 时可以先略看 ，在看第三部分时 在细看前两部分 ！！！

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 的结果一样。

 

 

饿。。。弄了2个小时终于写完啦，累死俺啦.......................