Mysql实战-left/right/inner join 使用详解及索引优化

前面我们讲解了B+Tree的索引结构，也详细讲解下Join的底层驱动表 选择原理，今天我们来了解一下为什么会出现内连接外连接，两种连接方式，另外实战一下内连接和几种最常用的join语法

• Left join 左表 left join 右表查询
• right join 左表 right join 右边查询
• inner join 两个表 inner join查询

  文章目录

  • • • Mysql实战-left/right/inner join 使用详解及索引优化
      • • 1.建表及测试数据
        • 2.为什么会有内连接和外连接
        • 2.外连接的连接条件和where条件有什么区别
        • 3.左连接 left join
        • 4.右连接 right join
        • 5.内连接 inner join

          1.建表及测试数据

          我们先创建两个表 test_user 和 test_order 这两个表作为我们的测试表及测试数据

          • test_user 5条数据, 索引只有主键id
          • test_order 5条数据，索引同样也只有主键id

            #创建test_user
            CREATE TABLE `test_user` (
              `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
              `user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名字',
              `age` int DEFAULT NULL COMMENT '年龄',
              PRIMARY KEY (`id`),
              KEY `idx_age` (`age`),
              KEY `idx_name` (`user_name`)
            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
            #创建表 test_order
            CREATE TABLE `test_order` (
              `id` int NOT NULL AUTO_INCREMENT,
              `user_id` int NOT NULL COMMENT '用户id，就是test_user的唯一主键id',
              `order_name` varchar(32) NOT NULL DEFAULT '订单信息',
              `pay` int NOT NULL DEFAULT '0',
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=1  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';
            

            插入数据

            #插入 user 用户数据
            INSERT INTO `prepare`.`test_user` (`id`, `user_name`, `age`) VALUES (1, 'aa', 10);
            INSERT INTO `prepare`.`test_user` (`id`,  `user_name`, `age`) VALUES (2, 'bb', 20);
            INSERT INTO `prepare`.`test_user` (`id`,  `user_name`, `age`) VALUES (3, 'cc', 30);
            INSERT INTO `prepare`.`test_user` (`id`, `user_name`, `age`) VALUES (4, 'dd', 40);
            #插入 order 订单数据
            INSERT INTO `prepare`.`test_order` (`id`, `user_id`, `order_name`, `pay`) VALUES (1, 1,'衣服', 100);
            INSERT INTO `prepare`.`test_order` (`id`, `user_id`, `order_name`, `pay`) VALUES (2, 2,'鞋子',  200);
            INSERT INTO `prepare`.`test_order` (`id`, `user_id`, `order_name`, `pay`) VALUES (3, 2,'电视',  300);
            

            看两个表的关系， 可以知道

            • user用户表有4个用户， aa,bb,cc,dd
            • order订单表有 1,2,3 个订单， aa一条衣服， bb用户一个鞋子，一个电视

              

              2.为什么会有内连接和外连接

              下面我们来看下下面的场景

              • 我们要查询 每个人买了什么东西， 花了多少钱 ？
              • 我们的 两个表的连接条件就是 test_user.id = test_order.user_id

                select test_user.id, test_user.user_name, test_order.order_name, test_order.pay from test_user, test_order where test_user.id = test_order.user_id;
                

                执行结果

                

                根据查询结果，我们可以很明确的知道

                • 1.aa的用户，买了衣服，花了100
                • 2.bb的用户，买了鞋子花了200，买了电视花了300
                • 3.我们查询不出来 cc 和 dd同学， 因为他们俩没买东西

                  但是如果是 我们要知道每一个人的消费情况， 即使没买东西的人也要展示出来，该如何处理？

                  对于上面的test_user和test_order,我们的需求就是 对于每一个同学，我都要知道他们的花费情况， 哪怕你没有买任何一个东西，我也要知道结果

                  这个问题的本质就是 如果驱动表中的记录，没有在被驱动表中查询到匹配的记录，也要返回结果，呈现出来

                  • 内连接
                    • 内连接的两个表，如果驱动表中的记录，在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集
                    • 我们上边用的where test_user.id = test_order.user_id ，这种连接方式就是内连接
                    • 未匹配的记录不会出现在结果中
                    • 外连接
                      • 外连接的两个表，如果驱动表的记录， 在被驱动表中没有匹配的记录，仍然要加入到结果集
                      • 未匹配的记录，都要加入结果集
                      • 左外连接 left join 语法
                      • 右外连接 right join语法

                        2.外连接的连接条件和where条件有什么区别

                        上面我们已经知道了 外连接就是 不匹配的记录也要返回结果

                        这个就会带来一个问题， 比如我的驱动表有100条记录，但是我的外连接 我不想让驱动表全部数据加入到结果集， 但是外连接的本质就是不匹配的就展示到结果集，这种情况该如何处理 ？

                        这就是 on 连接条件中的过滤条件 和 where 子句中的过滤条件的区别，我们可以通过 on连接条件的过滤条件和 where子句的过滤条件进行区分，解决上面的问题

                        • where子句的过滤条件
                          • where子句的过滤条件就是不论内连接，外连接，不符合where子句的，全部都不会被加入到最终结果集
                          • on连接条件的过滤条件
                            • 对于内连接来说 on连接条件的过滤条件和where子句的过滤条件一样对待，没有任何区别，二者是等价的
                            • 对于外连接来捉 on连接条件的过滤条件，如果在被驱动表中无法匹配on的过滤条件，该记录是要加入到结果集中
                            • 不符合匹配条件的被驱动表的数据，全部用NULL值填充

                              3.左连接 left join

                              下面我们来看下左外连接， 简称左连接 left join, 还是上面的两个表和数据,执行左连接

                              先纠正一个误区

                              !!! 左连接并不是说 左边的数据不动，右边匹配的拼上来

                              !!! 左连接并不是说 左边的数据不动，右边匹配的拼上来

                              !!! 左连接并不是说 左边的数据不动，右边匹配的拼上来

                              要按照错误的这个说法,下面的语句执行完 左边不动，还是4条，1，2，3，4 但是明显不是

                              左连接真正意义是 左边驱动表在右侧匹配找到，就拼数据，不匹配拼NULL

                              select * from test_user left  join test_order on test_user.id = test_order.user_id ;
                              explain select * from test_user left  join test_order on test_user.id = test_order.user_id ;
                              

                              执行结果

                              

                              

                              前面我们已经分析过 left join 哪个是驱动表，哪个是非驱动表, 从结果来看 test_user就是驱动表

                              • test_user用户表是 驱动表
                              • test_order 是被驱动表
                              • user表 有数据， id=1，2，3，4
                              • order表有 user_id 的数据， 1，2，2
                              • on 连接条件 id相同，test_user.id = test_order.user_id
                              • 查询结果 数据变成了5条数据
                              • 因为被驱动表 找到了2条 user_id = 2的 bb用户买了两次东西， 所以要展示2次， 变成了 1，2，2，3，4 五条数据
                              • 购买了东西的 1-aa, 2-bb 用户查询出来了
                              • 没有购买东西的 3-cc，4-dd 用户也查询出来了，只不过结果用NULL填充

                                4.右连接 right join

                                下面我们来看下右外连接， 简称右连接 right join, 还是上面的两个表和数据,执行右连接

                                select * from test_user right  join test_order on test_user.id = test_order.user_id ;
                                explain select * from test_user right  join test_order on test_user.id = test_order.user_id ;
                                

                                可以知道 test_order是驱动表，要在 test_user中找 和他匹配的数据

                                执行结果

                                

                                • test_order是驱动表
                                • test_user是被驱动表
                                • test_order 有 1，2，2 三条数据
                                • 匹配出来 全都在 被驱动表 user中存在
                                • 所以救国就是 1，2，2

                                  现在我们插入一条 不在 user的数据， user_id = 5, user中没有5的这个数据，只有id=1/2/3/4, 再执行右连接查询，看下结果

                                  INSERT INTO `test`.`test_order` (`id`, `user_id`, `order_name`, `pay`) VALUES (4, 5, 'xxx',  400);
                                  select * from test_user right  join test_order on test_user.id = test_order.user_id ;
                                  

                                  

                                  执行结果 可以明确看到， test_order.user_id =5这一条数据， 在被驱动表 test_user 中不存在id=5的数据， 所以 就用Null填充

                                  5.内连接 inner join

                                  最开始我们举例子 用的下面的例子 ，虽然没有明确 声明 inner join，但是本质就是内连接

                                  select * from test_user, test_order where test_user.id = test_order.user_id;
                                  

                                  

                                  上面的这种方式 和 下面2中本质一致, inner 关键字可以省略

                                  select * from test_user join test_order  where test_user.id = test_order.user_id;
                                  select * from test_user inner join test_order where test_user.id = test_order.user_id;
                                  #等价于
                                  select * from test_user, test_order where test_user.id = test_order.user_id;
                                  

                                  ---

                                  至此，我们已经彻底知道了 left join， right join ，inner join的区别和联系，这对于我们实际项目中SQL语句有至关重要的作用，一定要牢记于心

                                   
                                   网络工程师考试时间  四川建设网有限责任公司  室内设计培训  宁波网站建设布局  南昌网站建设方案  网页制作大宝库