Oracle的join..on连接区别

多表关联查询JOIN …ON的简例

一.建表

create table a(
id number(7),
name varchar2(20)
);
create table b(
id number(7),
name varchar2(20)
);

insert into a (id,name) values (1,'a1');
commit;
insert into a (id,name) values (2,'a2');
commit;
insert into a (id,name) values (3,'a3');
commit;
insert into a (id,name) values (4,'a4');
commit;

insert into b (id,name) values (1,'b1');
commit;
insert into b (id,name) values (2,'b2');
commit;
insert into b (id,name) values (5,'b5');
commit;
insert into b (id,name) values (6,'b6');
commit;

如图

二.笛卡尔积

返回结果数量是各个表记录的乘积, *代表from后面表中所有列,
结果是a每条记录与b每条记录结合形成

select * from a , b;

结果

补充
CROSS JOIN就是笛卡尔乘积连接，不需要任何关联条件，实现M*N的结果集，
其实这种SQL JOIN方式基本上只在理论上有意义，实际当中，很少有用的CORSS JOIN方式
注意
cross join跟inner join、outer join等有所不同，不需要关键词on，因为它不需要相关字段做关联。

select * from a cross join b;
select a.id, a.name, b.id, b.name from a cross join b;

结果

三.操作符(+)

在JOIN…ON之前的外连接写法
(+)所在表为补充表,另一方是主表

注意事项

1. （+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符
3. （+）操作符只适用于列，而不能用在表达式上。
4. （+）操作符不能与or和in操作符一起使用。
5. （+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

select * from a,b where a.id(+)=b.id;--b为主表,a为补充
select * from a,b where a.id=b.id(+);--a为主表,b为补充

结果

四.内连接

等值连接, 参与等值条件的两个字段值，相等时才作为结果返回。
使用[INNER] JOIN…ON…语法,建议采用JOIN…ON语法,
INNER JOIN和JOIN作用等价。内连接,等值连接是一个意思。

select a.id,b.id,a.name,b.name from a,b where a.id=b.id ;--等值连接
select a.id,b.id,a.name,b.name from a join b on (a.id=b.id) ;--join ..on
select a.id,b.id,a.name,b.name from a inner join b on (a.id=b.id) ; -- inner join ..on

结果

使用JOIN…USING语法(了解)
- 关联的两个表中需要有相同的字段.(名字和类型相同)
- 关联的字段在使用时不能加别名

select id,a.name,b.name from a join b using(id);--join ..using

结果

补充
NATURAL JOIN: 在连接条件中使用等于(=)运算符比较被连接列的列值，
但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列

select * from a natural join b ;

五.左外连接

等值连接，需要两个表的关联字段等值才将结果返回。
如果需要将某一个表记录全部返回，即使另一个表找不到对等字段记录，此时可以使用外连接。
A left outer join B on(…)
以A表记录显示为主,B表记录为补充.当A表记录在B表找不到对等记录时,B以NULL方式补充。

注意
a左外b=b右外a

select a.id,a.name, b.name,b.id from 
a left join b on (a.id = b.id); --left join .. on
select a.id,a.name, b.name,b.id from 
a left outer join b on (a.id = b.id); --left outer join .. on

结果

六.右外连接

A right outer join B on(…)
以B表记录显示为主,A表记录为补充.当B表记录在A表找不到对等记录时,A以NULL方式补充。

select a.id,a.name, b.name,b.id from 
a right join b on (a.id = b.id); -- right join .. on
select a.id,a.name, b.name,b.id from 
a right outer join b on (a.id = b.id); -- right outer join .. on

结果

七.全外连接

全外连接=左外连接+右外链接-(重复记录)

select a.id,a.name, b.name,b.id from a left outer join b on (a.id = b.id); --left outer join .. on

select a.id,a.name, b.name,b.id from a right outer join b on (a.id = b.id); -- right outer join .. on

select a.id,b.id ,a.name,b.name from a join b on (a.id=b.id) ;--join ..on

select * from a full outer join b on (a.id=b.id);--full outer join .. on  

结果

1.左外连接

2.右外连接

3.重复记录:内连接

4.全外连接