下面是一个小SQL模式来重现我的问题。

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的书。

=> 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)

=> 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)

=> 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的行吗？