【数据库笔记】MySQL&Oracle JOIN方法图码总结（续更）

文章目录

• 1 SQL JOINS
• 2 实例应用
• 3 总结

---

1 SQL JOINS

图片源于：https://www.runoob.com/sql/sql-join.html

2 实例应用

2.1.自建数据集

简化场景：假设武学道场中有一个天才班级，学生表（stu）有4名同学丁鹏，谢晓峰，姬无命和博主本人，还有1名新来的神秘绝世高手XX暂未登记进学生表，但这个高手也参加了本次考试，他们对应唯一学号（id）分别为1，2，3，4，5（神秘人）。成绩表（grade）记录了他们一次考试的成绩，博主因为太菜所以没有登记成绩。
建表代码如下：

--表1 : grade （id与grade不是一一对应）
create table grade
(id int,
grade int);
--表2 : grade_only（id与grade一一对应）
create table grade_only
(id int,
grade int);
--表3 : stu（id与name一一对应）
create table stu
(id int,
s_name varchar(255));

insert into grade values (1,99),(1,70),(2,100),(3,65),(5,999);
insert into grade_only values (1,99),(2,100),(3,65),(5,999);
insert into stu values (1,'丁鹏'),(2,'谢晓峰'),(3,'姬无命'),(4,'博主');

表1 : grade

id	grade
1	99
1	70
2	100
3	65
5	999

表2 : grade_only

id	grade
1	99
2	100
3	65
5	999

表3 : stu

id	s_name
1	丁鹏
2	谢晓峰
3	姬无命
4	博主

2.2 left join

左外连接，又称左连接。不仅返回满足条件的所有记录，而且还会返回不满足连接条件的连接操作符左边表的其他行 。本例中，左表是学生表stu。

法1：left join写法

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	LEFT JOIN grade g ON s.id = g.id 

法2：Oracle(+)的应用写法

Oracle(+)的应用详见：【数据库笔记】Oracle（+）号用法

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s,
	grade g 
WHERE
	s.id = g.id ( + )----强加成绩表空白，同时返回了缺成绩的学生姓名

id	s_name	grade
1	丁鹏	99
1	丁鹏	70
2	谢晓峰	100
3	姬无命	65
4	博主	(Null)

以下，只返回了缺成绩的学生姓名，就是博主本人了

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	LEFT JOIN grade g ON s.id = g.id 
WHERE
	g.id IS NULL

id	s_name	grade
4	博主	(Null)

2.3 right join

右外连接，又称右连接。表1右连接表2，以右为主，表示以表2为主，关联查询表1的数据，查出表2所有数据以及表1和表2有交集的数据。本例中，右表是成绩表grade。

法1：right join写法

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	right JOIN grade g ON s.id = g.id 

法2：Oracle(+)的应用写法

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s,
	grade g 
WHERE
	s.id( + ) = g.id ----强加学生表空白，同时返回了缺姓名的成绩

id	s_name	grade
1	丁鹏	99
1	丁鹏	70
2	谢晓峰	100
3	姬无命	65
(Null)	(Null)	999

以下，只返回了缺学生姓名的成绩，就是神秘人了

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	RIGHT JOIN grade g ON s.id = g.id 
WHERE
	s.id IS NULL

id	s_name	grade
(Null)	(Null)	999

2.4 inner join

inner join（join），内连接，表示以两个表的交集为主，查出来是两个表有交集的部分，其余没有关联就不额外显示出来。本例中，坐标是学生表stu，右表是成绩表grade。

法1：inner join写法

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	INNER JOIN grade g ON s.id = g.id 

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	grade g
	INNER JOIN stu s ON g.id = s.id --表g，s调换位置不影响结果

法2：where写法

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s,
	grade g 
WHERE
	s.id = g.id 

id	s_name	grade
1	丁鹏	99
1	丁鹏	70
2	谢晓峰	100
3	姬无命	65

这里插一段表stu链接表grade & 表grade_only的区别：重复的记录会交织。如stu表（1，丁鹏）出现m次，grade_only表（1，99）出现n次，那么JOIN（left / right / inner）结果会出现m*n次。如下例，m=3,n=2,那么inner join后会出现6行结果。

为了让结果更清晰，表1、表3增加了几行丁鹏的数据。

insert into grade values (1,99);
insert into stu values (1,'丁鹏');

表1: grade

id	grade
1	99
1	99
1	70
2	100
3	65
5	999

表2: grade_only

id	grade
1	99
2	100
3	65
5	999

表3 : stu

id	s_name
1	丁鹏
1	丁鹏
2	谢晓峰
3	姬无命
4	博主

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	INNER JOIN grade g ON s.id = g.id

id	s_name	grade
1	丁鹏	99
1	丁鹏	99
1	丁鹏	99
1	丁鹏	99
1	丁鹏	70
1	丁鹏	70
2	谢晓峰	100
3	姬无命	65

对比于表stu与与表grade_only 链接（id和grade如果是一一对应的情况）

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	INNER JOIN grade_only g ON s.id = g.id--

id	s_name	grade
1	丁鹏	99
1	丁鹏	99
2	谢晓峰	100
3	姬无命	65

2.5 full join

full join（full outer join），全外连接，只要左表和右表其中一个表中存在匹配，则返回行。本例中，坐标是学生表stu，右表是成绩表grade，此处用2.4节更新后的表。

在mysql中没有full join。我们可以使用union来达到目的。

法1：union写法（union去重）

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	LEFT JOIN grade g ON s.id = g.id 

UNION

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	RIGHT JOIN grade g ON s.id = g.id

法2：oracle full join 写法

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	FULL OUTER JOIN grade g ON s.id = g.id

id	s_name	grade
1	丁鹏	99
1	丁鹏	70
2	谢晓峰	100
3	姬无命	65
4	博主	(Null)
(Null)	(Null)	999

以下，返回了缺成绩的学生姓名 & 缺学生姓名的成绩，就是博主本人&神秘人了

法1：union写法（union去重）

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	LEFT JOIN grade g ON s.id = g.id 
WHERE
	g.id IS NULL 
	
UNION

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	RIGHT JOIN grade g ON s.id = g.id 
WHERE
	s.id IS NULL

法2：oracle full join 写法

SELECT
	s.id,
	s.s_name,
	g.grade 
FROM
	stu s
	FULL OUTER JOIN grade g ON s.id = g.id 
WHERE
	s.id IS NULL 
	OR g.id IS NULL

id	s_name	grade
4	博主	(Null)
(Null)	(Null)	999

3 总结

JOIN用得好，SQL没烦恼。

本文地址：https://blog.csdn.net/qq_36056219/article/details/108966914