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