文章目录
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的形式),在空余的时间进行总结记录,一步一个脚印,慢慢的进步