PostgreSQL LEFT JOIN与WHERE !=子句疑问：为何未返回id=2的记录？

复现用SQL Schema #

begin;

drop table if EXISTS book CASCADE ;
drop table if EXISTS author CASCADE ;
drop table if EXISTS book_author_asso CASCADE ;

create table book (
    id serial PRIMARY KEY,
    name varchar(100)
);

create table author (
    id serial PRIMARY KEY,
    name varchar(100)
);

create table book_author_asso (
    book_id integer references book(id),
    author_id integer references author(id)
);

insert into book (name) values ('toto'), ('toto2');
insert into author (name) values ('robin1'), ('robin2');
insert into book_author_asso (book_id, author_id) VALUES (1, 1), (1, 2);

commit;

需求与查询结果 #

需求为：查询作者id不等于1的书籍。执行以下三个查询后得到不同结果：

查询1： #

=> select * from book b left JOIN book_author_asso a on a.book_id=b.id where a.author_id != 1;
 id | name | book_id | author_id 
----+------+---------+-----------
  1 | toto |       1 |         2
(1 row)

查询2： #

=> select * from book b left join book_author_asso a on a.book_id=b.id where a.author_id is null or a.author_id != 1;
 id | name  | book_id | author_id 
----+-------+---------+-----------
  1 | toto  |       1 |         2
  2 | toto2 |         |          
(2 rows)

查询3： #

=> select * from book b left JOIN book_author_asso a on a.book_id=b.id and a.author_id != 1;
 id | name  | book_id | author_id 
----+-------+---------+-----------
  1 | toto  |       1 |         2
  2 | toto2 |         |          
(2 rows)

原因解释 #

第一个查询没返回id=2的toto2，核心是LEFT JOIN的执行顺序和NULL值的比较规则：

1. LEFT JOIN会先保留主表book的所有记录，对于没有匹配关联表book_author_asso的记录（比如id=2的书籍），关联表的字段会填充为NULL。
2. WHERE子句是在JOIN完成后执行过滤。第一个查询的条件a.author_id != 1中，NULL和任何值做比较的结果都是UNKNOWN，而WHERE子句会把UNKNOWN当作false处理，因此这条关联表字段全为NULL的记录被过滤掉了。

对比另外两个查询：

• 查询2额外加上了a.author_id is null，明确包含了那些没有关联作者的书籍，所以id=2的记录被保留。
• 查询3把a.author_id !=1放在了ON子句中，ON子句是在JOIN时筛选关联表的匹配记录，不会影响主表的记录保留，因此主表的id=2记录依然存在，只是关联表部分为NULL。

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