文章目录
- 1 SQL JOINS
- 2 实例应用
-
- 2.1.自建数据集
- 2.2 left join
- 2.3 right join
- 2.4 inner join
- 2.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
| 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