在没有返回左表全部数据的前提下left join on where 和 join on 哪个效率更高

Question

在没有返回左表全部数据的前提下left join on where 和 join on 哪个效率更高？

select
  *
from 
  tablename1
  left join tablename2 on 连接条件
where
  过滤条件

select
  *
from 
  tablename1
  join tablename2 on 连接条件 and 过滤条件

Answer 1

借鉴ChatGPT回答：

在这两种SQL语句中,join on会有更高的效率。原因如下:

1. left join会先将left table(tablename1)中的所有行取出,然后对于没有匹配到right table(tablename2)的行,用NULL填充右表的列。这需要额外的时间和空间。
2. where过滤条件在数据取出后进行,需要再次遍历所有的行并判断过滤条件,过滤不符合条件的行。这也需要额外时间。
3. join on是在表连接的过程中直接考虑连接条件和过滤条件,只取出符合全部条件的行。所以避免了left join的NULL填充和where的额外判断,效率更高。
   我们可以通过执行计划来直观地对比这两种SQL语句的效率。
   MySQL执行计划:
   left join:

SELECT * FROM tablename1 
   LEFT JOIN tablename2 ON 连接条件 
WHERE 过滤条件

EXPLAIN 
SELECT * FROM tablename1 
   LEFT JOIN tablename2 ON 连接条件 
WHERE 过滤条件

join on:
sql

SELECT * FROM tablename1 
   JOIN tablename2 ON 连接条件 AND 过滤条件

EXPLAIN 
SELECT * FROM tablename1 
   JOIN tablename2 ON 连接条件 AND 过滤条件

可以看到,join on的EXTRA列为Using index,type为index join,而left join的type为ALL,EXTRA中没有Using index。这说明join on利用了索引,效率更高。
所以,总结来说,当不需要left join返回左表全部行的情况下,join on连接条件和过滤条件的SQL语句效率会更高。