测试一下你真的理解数据库左连接了吗？

Left join on系列文章
测试一下你真的理解数据库左连接了吗？
SQL连接与筛选：解析left join on和where的区别及典型案例分析
深入剖析多个表left join on的执行步骤原理：实战案例解析与原理探讨

前言

之前一直以为自己对于数据库的各种连接真的懂了，最近在工作中，有个左连接的写法平常比较少写，所以不是很理解，于是决定自己实践一下，深刻理解左连接的原理

数据库数据及脚本

两张表，一个班级对应多个学生（一对多关系），以下是t_student和t_class的数据，t_student的class_id是t_class的外键

注意： 我的t_class表中，id为4的cname重复了，但是is_delete为1，说明是逻辑删除状态

DROP TABLE IF EXISTS `t_class`;
CREATE TABLE `t_class`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `is_delete` smallint(255) NOT NULL DEFAULT 0,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_class
-- ----------------------------
INSERT INTO `t_class` VALUES (1, 'Java', 0);
INSERT INTO `t_class` VALUES (2, 'Python', 0);
INSERT INTO `t_class` VALUES (3, 'C语言', 0);
INSERT INTO `t_class` VALUES (4, 'Java', 1);


DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `class_id` int(11) NULL DEFAULT NULL,
  `hobby` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES (1, '小王', 1, 'Python');
INSERT INTO `t_student` VALUES (2, '小红', 2, 'Java');
INSERT INTO `t_student` VALUES (3, '小明', 3, 'C语言');
INSERT INTO `t_student` VALUES (4, '小李', 4, 'Java');

数据库在运行时的执行顺序

这是数据库真正在运行时的底层执行顺序，必须要记住，这有利于我们写出更好的SQL！！！

(8)SELECT (9) DISTINCT (11)< Top Num> < select list>
(1) FROM [left_table]
(3)<join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH <CUBE | RollUP>
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

左连接概念及误区

先来一张数据库各种连接图，这里我们主要从左连接来深刻理解

左连接是左边表的所有数据都有显示出来，右边的表数据只显示共同有的那部分，没有对应的部分只能补空显示，所谓的左边表其实就是指放在left join的左边的表

误区

之前我一直脑子里的印象，是左连接以左表为主表，查询的数据最多就是左表的数据，没想到还可能大于左表的数量！！

比如下面这个语句，左表数据总共才4条，查询出来居然会有6条！！！这让我有点意外 （其实是自己没有深刻理解，还是太菜！！！），下面我会解释。

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.hobby = c.cname
ORDER BY
	s.id

最正常的左连接

这种没啥好说的，就是最普通的左连接，接下来我会在此基础上做多个变形

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.class_id = c.cid 

左连接的各种变形

大家可以先看我的sql语句，猜想一下结果是什么，验证自己是否真的理解了数据库的左连接！！！

1、左连接查询出来的数量大于左表的数量

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.hobby = c.cname
ORDER BY
	s.id

这个就是上面的误区，为啥会有6条呢？

认真观察一下数据，会发现有id为2和4的重复了两条，这是因为我们ON的条件是s.hobby = c.cname，而最主要的是t_calss的cname有两个都是Java,不过有一条是is_delete=1说明是逻辑删除了，所以我们进行ON连接的时候会寻找所有符合s.hobby = c.cname的数据，如果对于同一条左表数据（比如id=2），在右表能找到两个记录（cid=1和4的cname都是Java），就都会查询出来，数据显示的格式是：id=2的左表数据一样，但是右表数据会展示两个不一样的；对于id=4同理

在工作中，由于我们的数据库设计都会有is_delete字段，比如我们的cname是不允许重复的，但是删除后允许创建重复的，对于这条逻辑删除的Java重复的数据，其实我们可以右表加个AND条件过滤AND c.is_delete = 0 ,这样就是在右表是非删除的数据中查询符合s.hobby = c.cname 条件的数据!!!

这样我们的数据就看起来是正常的了

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.hobby = c.cname 
	AND c.is_delete = 0     #新加的条件
ORDER BY
	s.id	

2、LEFT JOIN ON 后面两个连表条件

这种是要同时满足s.class_id = c.cid AND s.hobby = c.cname 两个条件右边表才会有数据！只要有一个不符合，右表数据就为null

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.class_id = c.cid 
	AND s.hobby = c.cname 
ORDER BY
	s.id

3、 ON后面加AND条件：左表的非连接字段s.name=‘小李’

这个就是在左表是小李的数据中查找右表中复合s.hobby = c.cname

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.hobby = c.cname
	AND s.name='小李'  
ORDER BY
	s.id

4、 ON后面加AND条件：右表的非连接字段c.is_delete = 0

这条其实就是上面第一种的最终解决方法:在右表是非删除的数据中查询符合s.hobby = c.cname 条件的数据

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.hobby = c.cname 
	AND c.is_delete = 0     #新加的条件
ORDER BY
	s.id	

5、ON后面加AND条件：右表的连表字段c.cname = 'Java’或者左表的连表hobby字段s.hobby=‘Java’

这种是在连表的字段中（cname，hobby ）再加上AND条件

这个条件由于没有过滤非删除数据并且刚好是c.cname = 'Java'有两个重复了，所以也会找出所有的，导致数据大于左表数据，结果为6条

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.hobby = c.cname 
	AND c.cname = 'Java' 
ORDER BY
	s.id
#等价于
SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.hobby = c.cname
	AND s.hobby='Java'   
ORDER BY
	s.id

把条件改成c.cname = 'Python' 或者s.hobby='Python' ,这样由于cname数据没有重复，所以数据不会大于左表数据4条！！

6、ON直接写死连接属性 ON s.hobby =‘Python’

结果为7条,至于这个我也不是完全理解，我的猜想是根据s.hobby ='Python'（左表中id=1,hobby =‘Python’）和右表所有数据连接一遍,这个就是图中前4条数据，然后再把左表除了hobby以外的数据再和右表连接（左表剩下的就是id=2 3 4）

SELECT
	* 
FROM
	`t_student` s
	LEFT JOIN t_class c ON s.hobby ='Python' 
ORDER BY
	s.id	

把s.hobby ='Java' 改成Java,这时候就是10条了，按照我上面的猜想去理解，相信大家也能想明白了。当然我的可能是错的，欢迎大家指出，一起进步！

总结

平常工作学习中，可能碰到一些小的知识点，当时解决了，但是并没有真的深刻理解，我觉得可以把平常的想法记起来（推荐采用todo list的形式），在空余的时间进行总结记录，一步一个脚印，慢慢的进步