Mysql实战-SQL语句On和Where语句的区别

前面我们讲解了Join的底层驱动表 选择原理，也知道了基本的内连接外连接两种SQL查询表连接方式

但是我们再查询多表的时候on和where语句到底有什么区别?

• where是过滤条件 ,不满足where的一定不会出现在结果中
• on是连接条件, 对于内连接来说 on和where效果一致
• 对于外连接来说, 如果在被驱动表中无法匹配on的过滤条件，该记录是要加入到结果集中
• 不符合匹配条件的被驱动表的数据，全部用NULL值填充
• 先 on 再left join 再where
• 使用on关键字时，会先根据on后面的条件进行筛选，条件为真时返回该行
• on的优先级高于left join，所以left join关键字会把左表中没有匹配的所有行也都返回，然后生成临时表返回
• where对与行的筛选是在left join之后的，也就是生成临时表之后对临时表进行筛选

  下面我们来实战SQL演练一下

  文章目录

  • • • Mysql实战-SQL语句On和Where语句的区别
      • • 1.建表及测试数据
        • 2. 内连接的on连接过滤条件等同于where过滤条件
        • 3.left join 外连接 on 连接条件
        • 4.left join where 过滤条件
        • 5.更复杂的 on 和 where的对比

          1.建表及测试数据

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

          • test_user 5条数据, 索引只有主键id
          • test_order 3条数据，索引同样也只有主键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. 内连接的on连接过滤条件等同于where过滤条件

              当连接条件是 inner join内连接时, on连接的过滤条件 等同于 where 过滤条件

              也就是说 你把过滤条件 放到 on 语句后面 或者放到 where 语句后面,效果是一致的

              #on语句过滤条件
              select * from test_user inner join test_order on test_user.id = test_order.user_id;
              #where语句作为过滤条件
              select * from test_user inner join test_order where test_user.id = test_order.user_id;
              

              

              3.left join 外连接 on 连接条件

              left join外连接的时候, on 连接条件过滤 和 where 条件过滤 区别就很大了, on 条件是 被驱动表 不匹配的也要展示, 用NULL来填充

              但是 where语句就是 不满足的全部都过滤掉, 下面我们来实际看下效果

              • on语句 的过滤条件, 不符合的展示出来,用NULL填充

                #找出驱动表
                explain select * from test_user left join test_order on ( test_user.id = test_order.user_id)  and test_order.user_id = 2;
                #查询结果
                select * from test_user left join test_order on ( test_user.id = test_order.user_id)  and test_order.user_id = 2;
                

                查看结果

                • test_user是驱动表, 那么test_order就是被驱动表
                • on 条件是 ( test_user.id = test_order.user_id) and test_order.user_id = 2
                • 是否只返回了 test_order.user_id = 2 的数据 ? 并不是, user_id 不等于2的也都返回了
                • 只不过 她们的被驱动表数据 order 的数据 全都是 NULL填充的
                • 所以 on 后面的过滤条件, 不是做过滤的,而是做匹配的, 不匹配的用NULL填充

                  

                  4.left join where 过滤条件

                  前面我们看到了 用 on 去 连接两个表, 并且设置了 test_order.user_id = 2

                  但是返回结果 并不是 user_id = 2的数据, 而是 不匹配的数据用NULL来代替了

                  如果是 where 语句呢?

                  如果说 test_order.user_id = 2 挂在where语句后面 效果是什么样子呢?

                  #查看驱动表
                  explain
                  select * from test_user left join test_order on ( test_user.id = test_order.user_id)  where test_order.user_id = 2;
                  #执行查询语句
                  select * from test_user left join test_order on ( test_user.id = test_order.user_id)  where test_order.user_id = 2;
                  

                  查看执行结果

                  • test_user是驱动表, 那么test_order就是被驱动表
                  • on 条件是 ( test_user.id = test_order.user_id)
                  • where 条件是 where test_order.user_id = 2
                  • 数据结果只有 test_order.user_id = 2 的数据 才返回, 别的 都不返回
                  • 所以 where 后面的过滤条件, 就是做过滤的, 只要where不满足, 结果就不会满足

                    

                    

                    5.更复杂的 on 和 where的对比

                    如果 上面的例子 你还是没区分出来 on 和 where的 区别, 我们再来一个更加直观的, 一眼就看出来区别

                    #on 条件
                    select * from test_user left join test_order on  test_user.id = test_order.user_id  and test_order.pay > 100;
                    #where 条件
                    select * from test_user left join test_order on  test_user.id = test_order.user_id  where test_order.pay > 100;
                    

                    我们看下执行结果

                    • on条件查询
                      • pay > 100 的 数据返回
                      • pay <= 100的也有一条, 但是都用NULL填充了
                      • 返回了 驱动表 test_user 连接 被驱动表 test_order 的符合数据的所有数据 5条数据 且 >100 的 2条 正常展示
                      • pay <= 100的数据 用NULL填充
                      • on先执行 , 连接条件生成临时表, 所以数据就在那里了, 5条数据
                      • 然后 匹配 pay >100 的2条, 匹配展示, 其余的 全都 NULL填充
                      • where 条件查询
                        • 结果 只有2条数据 pay>100 的就2条数据
                        • where是基于临时表去过滤的
                        • 不满足的不会呈现到返回结果

                          

                          ---

                          至此,我们已经彻底分清楚了 on语句和where语句的区别, 这对于我们能够正确的处理业务,十分重要

                           
                           如何自己搭建一个网站  项目推广  烟台建站价格  网站推广方案策划  免费的域名解析  网站建设策划书