解析SQL中LEFTJOINON与ONAND条件的区别

在SQL查询中，当需要从两个或多个表中提取数据时，通常会使用JOIN操作来实现。LEFT JOIN是一种常见的JOIN类型，它确保即使右表中没有匹配的记录，左表中的所有记录也会被返回。本文将重点讨论在LEFT JOIN中使用ON条件与ON AND条件的区别。

LEFT JOIN ON...WHERE

SELECT *
FROM tb_students ts
LEFT JOIN tb_class tc ON ts.cid = tc.id
WHERE tc.classcode = '1801'
ORDER BY ts.id;

在这个查询中，SQL的执行过程分为两步：

1. 首先根据ON条件（ts.cid = tc.id）生成一个临时表。


2. 然后应用WHERE条件（tc.classcode = '1801'）对临时表进行过滤，最终返回满足条件的记录。

LEFT JOIN ON...AND

SELECT *
FROM tb_students ts
LEFT JOIN tb_class tc ON ts.cid = tc.id AND tc.classcode = '1801'
ORDER BY ts.id;

此查询的执行过程同样分为两步，但ON条件有所不同：

1. 根据ON条件（ts.cid = tc.id AND tc.classcode = '1801'）直接生成临时表。即使条件不完全满足，也会返回左表的所有记录。

结论

使用LEFT JOIN时，ON...AND与ON...WHERE条件的主要区别在于：

1. ON...WHERE：先生成临时表，再对临时表进行过滤。这种情况下，如果右表的记录不满足WHERE条件，则不会出现在最终结果中，这实际上改变了LEFT JOIN的行为。


2. ON...AND：在生成临时表的过程中就应用了所有的条件。即使右表的记录不满足条件，左表的所有记录仍然会被保留。


3. 这些行为的根本原因在于LEFT JOIN、RIGHT JOIN和FULL JOIN的特性，它们在连接时保证了左表、右表或两边表的记录都会被包含在结果集中。相比之下，INNER JOIN没有这样的特性，因此在ON和WHERE中放置条件不会影响结果集。