mysql left join的基本用法以及on与where的区别(mysql 字符串拼接)越早知道越好

随心笔谈1年前 (2023)发布 admin
113 0



我们在写sql语句的时候,总是无法避免使用到连接关键词,比如内连接、外连接。种类是很多的,我在这里贴上一张在别处找到的图:

在这里插入图片描述

这张图我认为是非常详细了,它展示出了SQL语句中常见的链接类型,以本文中的left join为例,网上是这么给定义的:LEFT JOIN 关键字会从左表 那里返回所有的行,即使在右表中没有匹配的行。

其实光从字面意思上来说的话,是比较好理解的,但是在使用的过程中,还是会有一些问题的,比如条件在on后面与在where后面,他们的结果是完全不一样的,接下来我们就从浅到深去了解下left join。

CREATE TABLE `class` (
`class_id` int NOT NULL,
`class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`class_grade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`class_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT;

score表:

CREATE TABLE `score` (
`class_id` int NOT NULL,
`stu_id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`score` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT;

他们各有数据:

在这里插入图片描述

在这里插入图片描述

Q1:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id。

我们对这个语句进行分析:左表是class表,右表是score表,两表的class_id为两表关联字段。当左表class_id为1时,右表有两条记录的class_id为1;当左表class_id为2时,右表有两条记录的class_id为2;当左表class_id为3时,右表有一条记录的class_id为3,所以我们应当得到五条记录:

1 语文 A A002 82
1 语文 A A001 91
2 数学 B A002 87
2 数学 B A001 95
3 英语 C B003 65

Q2:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and s.score=90。

以第一题的例子为基础,这个又多了个s.score=90的条件,这个表示右表score的score字段为90,但是我们看表的数据,发现右表没有score为90,ok,那结果会不会是空呢?毕竟右表是没有符合条件的数据的。

事实上,如果执行这条sql语句的话,最终是可以得到结果的,只不过只会得到三条数据,左表中的字段全部显示,右表中的字段全部对应为空,这是因为右表中没有记录的score是90,所以右表的字段才是空的。

on是先对数据进行过滤,然后再进行连接,而where是在两表进行关联查询之后,再进行过滤的,on与where的区别就在这里,之后的例子也会涉及这两个区别的。

1 语文 A
2 数学 B
3 英语 C

Q3:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id where c.class_name=‘语文’ and s.score=90。

这个语句里面就涉及到了where关键字了,因此是需要注意与on的不同。这个sql的查询结果为空,这是因为先将两表连接查询出结果,然后再进行过滤,而连表查询出的结果是没有记录符合class_name=‘语文’,并且score=90,所以查询结果为空。

Q4:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and 1=0。

我们有时在sql的where条件后面会加上1=1,而这里的1=0则是表示两表关联失败,所以这个的结果只会显示出左表的数据。

1 语文 A null null
2 数学 B null null
3 英语 C null null

Q5:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on 1=0。

这个与上面的sql语句的执行结果是一样的,其实都是将左表的内容全部显示。

1 语文 A null null
2 数学 B null null
3 英语 C null null

Q6:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘语文’。

这个需要在右表中过滤出class_name为语文的记录,有两条,然后左表去连接这两条记录,所以不难看出,有四条结果:

1 语文 A A002 82
1 语文 A A001 91
2 数学 B null null
3 英语 C null null

Q7:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘英语’。

分析同上一题(空白的地方的结果为null):

1 语文 A
2 数学 B
3 英语 C B003 65

Q8:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘体育’。

右表中是没有数据的class_name为体育的,所以右表为空,显示左表的全部数据,右表对应的字段为空:

1 语文 A
2 数学 B
3 英语 C

Q9:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘语文’ and s.score=91。

右表中只有一条记录的score为91,所以需要拿左表与右表的这一条数据进行关联,左表只有语文可以与右表的那一条数据对上,所以结果为:

1 语文 A A001 91
2 数学 B
3 英语 C

Q10:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘体育’ and s.score=90。

右表中没有数据的score为90,同样左表中也没有class_name为体育,但是这并不意味着最后的结果就是空了,只要没有where条件,最终的结果数量最起码也会是左表中原先的数据数量,所以这条sql会返回左表的全部数据。

1 语文 A
2 数学 B
3 英语 C

Q11:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id where c.class_name=‘英语’。

需要注意的点:条件是在where中的,也就是在表关联之后,再进行过滤的,所以最终的结果只会有一条:

3 英语 C B003 65

Q12:select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id where s.score=91。

和上面一样,是在连表查询之后,找出score=90的数据:

1 语文 A A001 91

Q12;select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id where c.class_name=‘语文’ and s.score=91。

我们将两表连接查询后,找出结果中class_name为语文,score为91的记录,只有一条:

1 语文 A A001 91

通过这12道sql语句,其实就可以基本上掌握left join的使用以及可能出现的场景了,尤其是on与where在执行sql语句时,条件位置不同,最终的结果也不大相同。我觉得需要记住,只要没有where条件,那么最终结果的数量最起码与左表的数据数量相同,不可能会出现最终的结果为空的情况。

您可能感兴趣的文章:图文详解Mysql使用left?join写查询语句执行很慢问题的解决mysql使用left?join连接出现重复问题的记录MySQL中多个left?join?on关联条件的顺序说明关于mysql?left?join?查询慢时间长的踩坑总结MYSQL Left Join优化(10秒优化到20毫秒内)解决Mysql的left join无效及使用的注意事项说明mysql left join快速转inner join的过程mysql高效查询left join和group by(加索引)详解mysql 使用left join添加where条件的问题分析mysql中left join设置条件在on与where时的用法区别分析MySQL 8.0.18 Hash Join不支持left/right join左右连接问题MySQL left join操作中on和where放置条件的区别介绍mysql多个left join连接查询用法分析MySQL利用profile分析慢sql详解(group left join效率高于子查询)MySQL在右表数据不唯一的情况下使用left join的方法MySQL表LEFT JOIN左连接与RIGHT JOIN右连接的实例教程

© 版权声明

相关文章