文章目录
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 王五 女