PostgreSQL表连接时ON与WHERE子句的效率对比疑问

我有customer和coupons两张表，customer表中的reward_id为可空列，一个客户可拥有多张优惠券，优惠券属于特定客户。表结构如下：

+-------------+------------+
| coupons     | customers  |
+-------------+------------+
| id          | id         |
| customer_id | first_name |
| code        | reward_id  |
+-------------+------------+
-- coupons表的customer_id列已建立索引

最初我用这样的SQL进行表连接：

select c.*, cust.id as cust_id, cust.first_name as cust_name
from coupons c
join customer cust on c.customer_id = cust.id and cust.reward_id is not null

由于reward_id字段没有索引，我考虑将cust.reward_id is not null移至WHERE子句，写法如下：

select c.*, cust.id as cust_id, cust.first_name as cust_name
from coupons c
join customer cust on c.customer_id = cust.id
where cust.reward_id is not null

我想知道第二种写法是否比第一种更高效。

好问题！这两种写法在逻辑上是完全等价的，都会返回相同的结果集，但从性能角度来看，第二种写法通常会更高效，原因主要有这几点：

1. 执行顺序与优化器的选择空间 #

当你把过滤条件cust.reward_id is not null放在JOIN子句里时，部分数据库的查询优化器可能会优先对customer表执行全表扫描，先过滤掉reward_id为空的记录，再和coupons表进行连接。如果customer表数据量很大，这种全表扫描的成本会非常高。

而把过滤条件移到WHERE子句后，优化器更倾向于先利用coupons表中customer_id的索引，快速匹配到对应的customer记录，之后再对这些匹配到的记录进行reward_id is not null的过滤。这种路径避免了对整个customer表的全表扫描，尤其是当coupons表的匹配记录远少于customer表总数据量时，性能提升会很明显。

2. 无索引字段的过滤成本 #

由于reward_id没有索引，无论哪种写法都需要对匹配到的customer记录进行逐行判断，但第二种写法的过滤范围更小——只需要过滤那些已经和coupons匹配上的客户，而不是整个customer表的所有记录。

3. 用执行计划验证最终性能 #

当然，不同数据库的优化器行为可能略有差异，最准确的方式是用EXPLAIN命令查看两种写法的执行计划：

-- 查看第一种写法的执行计划
EXPLAIN select c.*, cust.id as cust_id, cust.first_name as cust_name
from coupons c
join customer cust on c.customer_id = cust.id and cust.reward_id is not null;

-- 查看第二种写法的执行计划
EXPLAIN select c.*, cust.id as cust_id, cust.first_name as cust_name
from coupons c
join customer cust on c.customer_id = cust.id
where cust.reward_id is not null;

对比两个执行计划，如果第二种写法的步骤里没有出现对customer表的全表扫描，而是先通过索引匹配再过滤，那性能肯定更优。

总的来说，在reward_id无索引的场景下，把过滤条件移到WHERE子句是更稳妥的选择，能让优化器选择更高效的执行路径。

内容的提问来源于stack exchange，提问作者tkhuynh