on条件用于定义join的匹配规则,决定哪些行被组合在一起;where条件用于过滤join产生的结果集。对于inner join,两者通常等价,但语义不同:on先过滤再连接,where后过滤。对于left/right/full outer join,on条件不影响外连接的保留行为,而where条件会过滤掉不满足的行,可能导致外连接退化为内连接。

INNER JOIN中ON与WHERE的等价性
在INNER JOIN中,ON条件和WHERE条件在逻辑上通常是等价的,因为它们都参与最终结果的筛选。例如,使用ON a.id = b.id AND a.status = 'active'与ON a.id = b.id WHERE a.status = 'active'会产生相同的结果集。这是因为INNER JOIN只返回匹配的行,任何不满足条件的行都会被丢弃。

SELECT * FROM table_a a INNER JOIN table_b b ON a.id = b.id WHERE a.status = 'active'; -- 预期输出:仅包含a.id=b.id且a.status='active'的行
LEFT JOIN中ON与WHERE的差异
在LEFT JOIN中,ON条件仅用于确定右表如何与左表匹配,而WHERE条件会过滤最终结果。如果WHERE条件引用左表列,它不会改变外连接的保留行为;但如果引用右表列,可能会意外地将外连接转换为内连接。例如,ON a.id = b.id AND b.value > 10中的b.value > 10不会影响左表的保留,但WHERE b.value > 10会过滤掉右表不匹配的行,导致左表中无匹配的行也被丢弃。

SELECT * FROM table_a a LEFT JOIN table_b b ON a.id = b.id WHERE b.value > 10; -- 预期输出:仅包含b.value>10的行,左表无匹配的行被丢弃(退化为INNER JOIN)
具体输入下ON与WHERE对NULL值的影响
当右表存在NULL值时,ON条件中的a.id = b.id会跳过NULL匹配,而WHERE条件中的b.value IS NOT NULL会显式过滤NULL。例如,若table_b中有id=1, value=NULL,则ON a.id = b.id不会匹配该行,而WHERE b.value IS NOT NULL会排除该行。这体现了ON和WHERE在处理NULL时的不同行为。










