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

文章目录

  • 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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐