前面我们讲解了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演练一下
1.建表及测试数据
我们先创建两个表 test_user 和 test_order 这两个表作为我们的测试表及测试数据
- test_user 5条数据, 索引只有主键id
- test_order 3条数据，索引同样也只有主键id
根据表记录 可以知道
- user用户表有4个用户， aa,bb,cc,dd
- order订单表有 1,2,3 个订单， aa一条衣服， bb用户一个鞋子，一个电视
2. 内连接的on连接过滤条件等同于where过滤条件
当连接条件是 inner join内连接时, on连接的过滤条件 等同于 where 过滤条件
也就是说 你把过滤条件 放到 on 语句后面 或者放到 where 语句后面,效果是一致的
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填充
5.更复杂的 on 和 where的对比
如果 上面的例子 你还是没区分出来 on 和 where的 区别, 我们再来一个更加直观的, 一眼就看出来区别
我们看下执行结果
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是基于临时表去过滤的
不满足的不会呈现到返回结果
————————————————
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/u010134642/article/details/134276061