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

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

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

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

借鉴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语句效率会更高。

以下是，这两种场景的使用条件。
在 SQL 中，LEFT JOIN ON WHERE 和 JOIN ON 是两种不同的连接查询方式，它们的使用场景和效率略有不同。

JOIN ON 是最常用的连接查询方式，在连接查询中使用条件时，建议将条件放到 ON 子句中，将连接的条件和过滤条件分开，这样会提升查询效率。这是因为在使用条件时，JOIN ON 会先使用 ON 子句中的条件进行表连接，生成的结果集再应用 WHERE 子句的条件进行结果过滤。

例如，下面的查询将 orders 表和 customers 表通过 customerID 列连接，并且使用 WHERE 子句添加了一个额外的条件。

SELECT o.OrderID, c.CustomerName
FROM Orders o
JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE c.City = 'London';

如果将 WHERE 子句中的条件移动到 ON 子句中，如下所示，它们将产生相同的结果，但是前者会导致不必要的性能下降。

SELECT o.OrderID, c.CustomerName
FROM Orders o
JOIN Customers c
ON o.CustomerID = c.CustomerID AND c.City = 'London';

LEFT JOIN ON WHERE 是使用 LEFT JOIN 表连接时的一种常见写法。LEFT JOIN 操作需要匹配左表中所有的行，因此在一些情况下使用 WHERE 子句进行过滤是很有必要的。但是，如果在使用 WHERE 子句时过滤的是右表中的列，而不是左表中的列，这时就会导致对左表中的所有行进行匹配，从而降低查询效率。

总之，在使用连接查询的过程中，建议在使用条件时将条件放到 ON 子句中，从而提高查询效率。只有在使用 LEFT JOIN 表连接并且需要进行右表过滤时，才使用 LEFT JOIN ON WHERE 的写法。