文章目录

1 SQL JOINS2 实例应用2.1.自建数据集2.2 left join2.3 right join2.4 inner join2.5 full join 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

idgrade19917021003655999

表2 : grade_only

idgrade19921003655999

表3 : stu

ids_name1丁鹏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 ( + )----强加成绩表空白，同时返回了缺成绩的学生姓名 ids_namegrade1 丁鹏991 丁鹏702谢晓峰1003姬无命654博主(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 ids_namegrade4博主(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 ----强加学生表空白，同时返回了缺姓名的成绩 ids_namegrade1 丁鹏991 丁鹏702谢晓峰1003姬无命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 ids_namegrade(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 ids_namegrade1 丁鹏991 丁鹏702谢晓峰1003姬无命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

idgrade1991 99 17021003655999

表2: grade_only

idgrade19921003655999

表3 : stu

ids_name1丁鹏1 丁鹏 2谢晓峰3姬无命4博主 SELECT s.id, s.s_name, g.grade FROM stu s INNER JOIN grade g ON s.id = g.id ids_namegrade1 丁鹏991 丁鹏991 丁鹏991 丁鹏991 丁鹏701 丁鹏702谢晓峰1003姬无命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-- ids_namegrade1 丁鹏991 丁鹏992谢晓峰1003姬无命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 ids_namegrade1 丁鹏991 丁鹏702谢晓峰1003姬无命654博主(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 ids_namegrade4博主(Null)(Null)(Null)999

3 总结

JOIN用得好，SQL没烦恼。