小菜学习日记—关于inner join left join联查过滤条件放在on还是where中的问题小菜学习日记—DB

小菜学习日记—关于inner join left join联查过滤条件放在on还是where中的问题昨天在写一个简单的有过滤条件联查SQL的时候，突然脑子一抽开始纠结过滤条件要写在哪？A inner join B on A.* = B.* and (我是过滤条件) 还是 A inner join B on A.* = B.* where (我是过滤条件) 。着实纠结了一会，然后查了资料自己也简单测试了一下，与大家分享一下：

联查SQL过滤条件的两种写法
简单结论
原因分析

联查 SQL过滤条件的两种写法写法1：过滤条件写在where中

SELECT u.user_id, u.user_name, u.email, u.phone, u.description FROM USER u INNER JOIN user_oauth o ON u.user_id = o.user_id WHERE o.oauth_id = "1"

写法2：过滤条件写在on后面的and中

SELECT u.user_id, u.user_name, u.email, u.phone, u.description FROM USER u INNER JOIN user_oauth o ON u.user_id = o.user_id AND o.oauth_id = "1"

简单结论

对于inner join 两种写法在查询结果上没有区别。
对于left join和right join 两种写法在查询结果上是不一致的。

原因分析 join联查可以简单理解为以下过程：

首先两个表做一个笛卡尔积。
然后根据on后面的条件对这个笛卡尔积做一个过滤形成一张临时表。
如果有where就对上一步的临时表再进行过滤，进而得到最终的结果集。

也就是说如果inner join left join联查过滤条件放在on中就是在第二步进行的过滤；如果过滤条件放在where中就是在第三部进行的过滤。
对于inner join 两种写法在查询结果上没有区别对于inner join内连接而言，无论是把过滤条件写在on中还是where中在结果集上都是没有区别的。因为inner join对笛卡尔积做过滤生成的临时表，其中on后面的条件是对左右两个表同时生效的。所以说无论是从第二步进行过滤还是从第三步进行过滤，效果是一样的。
请大家看下面的例子。
两个测试表 Boy Girl。字段为班级和姓名。
Boy

小菜学习日记—关于inner join left join联查过滤条件放在on还是where中的问题

文章图片

Girl

文章图片

inner join 生成的结果集

SELECT * FROM boy b INNER JOIN girl g

文章图片

inner join on 生成的结果集

SELECT * FROM boy b INNER JOIN girl g ON b.class = g.class

文章图片

inner join on + and过滤条件生成的结果集

SELECT * FROM boy b INNER JOIN girl g ON b.class = g.class AND g.class = '1'

文章图片

inner join on + where过滤条件生成的结果集

SELECT * FROM boy b INNER JOIN girl g ON b.class = g.class WHERE g.class = '1'

文章图片

对于left join和right join 两种写法在查询结果上是不一致的 inner join内连接是没有左右某部分为null的情况的，而对于left join和right join左右连接而言存在左右某部分为null的情况。
以left join左连接为例 A left join B，如果你把过滤条件写在on中，on后面的条件只对右表B有效，那最终结果集中这个限制对A是没有影响的，因为就算是B中的数据被过滤了，A中的数据仍旧可以匹配null来展示（左连接性质）。同理right join也是一样。
如果你把过滤条件写在where中，因为where中的过滤是上面所说的过程中的第三步，是对第二步生成的临时表的过滤，所以会直接体现到对最后结果的限制，也就是等同于对AB左右两部分同时限制的目的。
请大家看下面的例子。
表还是那俩。。。
left join on 生成的结果集

SELECT * FROM boy b LEFT JOIN girl g ON b.class = g.class

【小菜学习日记—关于inner join left join联查过滤条件放在on还是where中的问题】

文章图片

left join on + and 过滤条件生成的结果集

SELECT * FROM boy b LEFT JOIN girl g ON b.class = g.class AND b.class = '1'

文章图片

left join on + where过滤条件生成的结果集

SELECT * FROM boy b LEFT JOIN girl g ON b.class = g.class WHERE b.class = '1'

文章图片

结果已经比较明显了，过于右连接与左连接同理，这里就不赘述了。
- 对于inner join 两种写法在查询结果上没有区别。
- 对于left join和right join 两种写法在查询结果上是不一致的。
另外说明下，本文讨论的两种写法的区别不涉及性能相关问题，只是关注查询结果。
关于性能相关：小菜也自己查过一些资料，好像性涉及简单sql优化和具体数据结构和数据量的情况，小菜也没什么实际经验自己也比较模糊，希望有大牛路过的时候顺手指导指导啊。