mysql left join的基本用法及on与where的区别是什么

mysql left join的基本用法及on与where的区别是什么

前言

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

这张图我认为是非常详细了，它展示出了SQL语句中常见的链接类型，以本文中的left join为例，网上是这么给定义的：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 821 语文 A A001 912 数学 B A002 872 数学 B A001 953 英语 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的不同，因为这个语句涉及到了where关键字。这个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 null2 数学 B null null3 英语 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 null2 数学 B null null3 英语 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 821 语文 A A001 912 数学 B null null3 英语 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 912 数学 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登录后复制登录后复制

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。