Oracle 表连接详解（left join、left outer join）

1 概述

结果集（红色部分）如下：

2 示例

2.1 jion：内连接

with t_test1 as (
  select 1 sno, '张三' sname, '女' sex from dual union all 
  select 2 sno, '李四' sname, '男' sex from dual union all
  select 3 sno, '王五' sname, '女' sex from dual
), t_test2 as (
  select 1 sno, '武汉市' city from dual union all 
  select 4 sno, '咸宁市' city from dual
)
select t1.*, t2.*
  from t_test1 t1
  join t_test2 t2 on t2.sno = t1.sno;

查询结果：

SNO		SNAME	SEX		SNO		CITY
1		张三		女		1		武汉市

2.2 left join：左连接

with t_test1 as (
  select 1 sno, '张三' sname, '女' sex from dual union all 
  select 2 sno, '李四' sname, '男' sex from dual union all
  select 3 sno, '王五' sname, '女' sex from dual
), t_test2 as (
  select 1 sno, '武汉市' city from dual union all 
  select 4 sno, '咸宁市' city from dual
)
select t1.*, t2.*
  from t_test1 t1
  left join t_test2 t2 on t2.sno = t1.sno;
  -- 仅修改 left join

查询结果：

SNO		SNAME	SEX		SNO		CITY
1		张三		女		1		武汉市
2		李四		男
3		王五		女

2.3 right join：右连接

with t_test1 as (
  select 1 sno, '张三' sname, '女' sex from dual union all 
  select 2 sno, '李四' sname, '男' sex from dual union all
  select 3 sno, '王五' sname, '女' sex from dual
), t_test2 as (
  select 1 sno, '武汉市' city from dual union all 
  select 4 sno, '咸宁市' city from dual
)
select t1.*, t2.*
  from t_test1 t1
 right join t_test2 t2 on t2.sno = t1.sno;
  -- 仅修改 right join

查询结果：

SNO		SNAME	SEX		SNO		CITY
1		张三		女		1		武汉市
                 		4		咸宁市

2.4 full join：全连接

with t_test1 as (
  select 1 sno, '张三' sname, '女' sex from dual union all 
  select 2 sno, '李四' sname, '男' sex from dual union all
  select 3 sno, '王五' sname, '女' sex from dual
), t_test2 as (
  select 1 sno, '武汉市' city from dual union all 
  select 4 sno, '咸宁市' city from dual
)
select t1.*, t2.*
  from t_test1 t1
  full join t_test2 t2 on t2.sno = t1.sno;
  -- 仅修改 full join

查询结果：

SNO		SNAME	SEX		SNO		CITY
1		张三		女		1		武汉市
2		李四		男
3		王五		女
                 		4		咸宁市

3 扩展

3.1 left outer join 与 left join 没有区别！

简称，常用	全称，了解即可
join	inner join
left join	left outer join
right join	right outer join
full jion	full outer join

3.2 写法：(+)

• (+) 在哪边，就是 左连接 哪边
• 示例 2.2 等同于下：

with t_test1 as (
  select 1 sno, '张三' sname, '女' sex from dual union all 
  select 2 sno, '李四' sname, '男' sex from dual union all
  select 3 sno, '王五' sname, '女' sex from dual
), t_test2 as (
  select 1 sno, '武汉市' city from dual union all 
  select 4 sno, '咸宁市' city from dual
)
select t1.*, t2.*
  from t_test1 t1,
       t_test2 t2
 where t1.sno = t2.sno(+);

查询结果：

SNO		SNAME	SEX		SNO		CITY
1		张三		女		1		武汉市
2		李四		男
3		王五		女