oracle先 内连接之后再left outer join,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 t1inner 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 t1left join test2 t2on t1.id=t2.id ; 或 select * from test1 t1left outer join test2 t2on 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 t2on t1.id=t2.id ; 或 select * from test1 t1right outer join test2 t2on t1.id=t2.id ;

ID NAME ID COURSE

---------- ---------- ---------- ----------

1 gg1 1 qq1

2 gg2 2 qq2

6 qq6

6、full join

select * from test1 t1full join test2 t2on t1.id=t2.id; 或 select * from test1 t1full outer join test2 t2on 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个小时终于写完啦，累死俺啦.......................