PostgreSQL LEFT JOIN筛选时机不同导致结果差异的疑问

问题场景 #

我完全无法理解以下PostgreSQL数据库的查询结果：

a) 仅在连接后筛选行 #

SELECT COUNT(*)
FROM apidata.info x
LEFT JOIN compdata.info a
    ON x.info = a.identifier
WHERE
    a.permit_id IS NULL
    AND a.identifier IS NULL;

查询结果：

count
--------
109242
(1 row)

b) 仅在连接前筛选行 #

SELECT COUNT(*)
FROM apidata.info x
LEFT JOIN compdata.info a
    ON x.info = a.identifier
    AND a.permit_id IS NULL
WHERE a.identifier IS NULL;

查询结果：

count
--------
111981
(1 row)

c) 在连接前后均筛选行 #

SELECT COUNT(*)
FROM apidata.info x
LEFT JOIN compdata.info a
    ON x.info = a.identifier
    AND a.permit_id IS NULL
WHERE
    a.permit_id IS NULL
    AND a.identifier IS NULL;

查询结果：

count
--------
111981
(1 row)

疑问：为何查询b和c的计数会高于查询a？尤其预期查询c的计数不会超过a，但它与查询b结果完全相同。源表有数百万行，我无法构想产生类似结果的简单数据集，请问我忽略了什么？能否提供简化示例？

核心原因：LEFT JOIN中ON与WHERE的逻辑差异 #

LEFT JOIN的执行顺序是：

1. 以左表为基础，根据ON条件匹配右表行，无法匹配的右表字段填充为NULL
2. 对连接后的结果集，应用WHERE条件进行最终筛选

三个查询的逻辑拆解 #

1. 查询a：
   ON条件仅匹配x.info = a.identifier，连接后右表要么是匹配到的有效行（a.identifier非空），要么是未匹配的全NULL行。WHERE条件要求a.permit_id IS NULL AND a.identifier IS NULL，这意味着只统计左表中完全没有匹配到右表任何行的记录。

2. 查询b：
   ON条件额外添加了a.permit_id IS NULL，连接阶段就只匹配右表中permit_id为NULL的行。如果左表某条记录在右表有匹配行，但这些行的permit_id都不为NULL，连接后右表字段会被填充为NULL，这部分记录会被WHERE的a.identifier IS NULL统计进来。而查询a中，这部分记录会匹配到右表的行，因此被WHERE条件排除，所以b的计数比a多。

3. 查询c：
   ON条件和b一致，WHERE条件同时要求a.permit_id IS NULL AND a.identifier IS NULL。但由于ON阶段已经过滤掉了a.permit_id非空的右表行，连接后右表为NULL的行必然满足a.permit_id IS NULL，因此这个WHERE条件是冗余的，结果和b完全相同。

简化示例验证 #

我们用小型模拟表来复现这个逻辑：

左表 apidata.info（别名x） #

右表 compdata.info（别名a） #

查询a的结果：COUNT=1

• 连接后：x.info=1匹配到a的两行，x.info=2匹配到a的一行，x.info=3无匹配（a字段全NULL）
• WHERE筛选后：仅x.info=3符合条件

查询b的结果：COUNT=2

• 连接后：x.info=1匹配到a中permit_id为NULL的行；x.info=2无符合ON条件的匹配行（a字段全NULL）；x.info=3无匹配（a字段全NULL）
• WHERE筛选后：x.info=2和x.info=3符合条件

查询c的结果：COUNT=2

• 连接逻辑和b完全相同
• WHERE筛选时，x.info=2和3的a字段全NULL，同时满足a.permit_id IS NULL和a.identifier IS NULL，结果和b一致

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