LEFT JOIN查询排除collection_id=2却返回book_id=1，求问题原因

需求 #

获取所有未被加入到collection_id=2的书籍，但当前查询返回了属于该集合的book_id=1，不符合预期。

数据表结构与测试数据 #

CREATE TABLE db.books (
    id INT auto_increment not NULL,
    title varchar(100) NULL,
    category_id INT NULL,
    PRIMARY  key (id)
)

CREATE TABLE db.books_categories (
    id INT auto_increment not NULL,
    category varchar(100) NULL,
    PRIMARY  key (id)
)

CREATE TABLE db.collection (
    id INT auto_increment not NULL,
    title varchar(100) NULL,
    PRIMARY  key (id)
)

CREATE TABLE db.collections_has_books (
    id INT auto_increment not NULL,
    collection_id int not NULL,
    book_id int not NULL,
    PRIMARY  key (id)
)

ENGINE=InnoDB
DEFAULT CHARSET=latin1
COLLATE=latin1_swedish_ci;

INSERT INTO db.books (title,category_id) VALUES
     ('Harry Potter and the philosopher''s stone',2),
     ('The lord of the rings',2),
     ('Moby dick',3),
     ('Robinson Crusoe',3),
     ('The Time Machine',1),
     ('The Great Gatsby',4),
     ('The Treasure Island',3);

INSERT INTO db.books_categories (category) VALUES
     ('science_fiction'),
     ('fanstasy'),
     ('adventures'),
     ('other');

INSERT INTO db.collection (title) VALUES
     ('books for vacations'),
     ('books for teenagers'),
     ('first readings'),
     ('books about adventrues and other stuff');

INSERT INTO db.collections_has_books (collection_id, book_id) VALUES
     (1,1),
     (1,4),
     (1,5),
     (2,1),
     (2,2),
     (2,4),
     (3,4),
     (3,5),
     (4,4),
     (4,5),
     (4,6);

错误查询语句 #

SELECT distinct `b`.* FROM `books` `b` 
LEFT JOIN `books_categories` `c` 
ON c.id = b.category_id 
LEFT JOIN `collections_has_books` `chb` 
ON chb.book_id = b.id 
WHERE (chb.collection_id != 2)  

错误结果 #

错误原因 #

book_id=1同时存在于collection_id=1和2中，执行LEFT JOIN collections_has_books后会生成两条关于该书籍的记录：一条关联到collection_id=1，另一条关联到collection_id=2。

WHERE条件chb.collection_id !=2会过滤掉关联到collection_id=2的那条记录，但保留关联到collection_id=1的记录。最后DISTINCT去重时，book_id=1的记录依然会被保留，因为存在符合WHERE条件的关联记录。

另外，查询中LEFT JOIN books_categories但未使用该表的任何字段，对结果无影响，可以移除。

正确查询方案 #

方案一：使用NOT EXISTS（逻辑最直观） #

直接判断当前书籍不存在于collection_id=2的关联记录中：

SELECT b.* 
FROM db.books b
WHERE NOT EXISTS (
    SELECT 1 
    FROM db.collections_has_books chb
    WHERE chb.book_id = b.id 
      AND chb.collection_id = 2
);

方案二：使用LEFT JOIN + IS NULL #

将collection_id=2的条件放在JOIN的ON子句中，再筛选无关联记录的书籍：

SELECT DISTINCT b.*
FROM db.books b
LEFT JOIN db.collections_has_books chb 
  ON chb.book_id = b.id 
  AND chb.collection_id = 2
WHERE chb.id IS NULL;

方案三：使用GROUP BY + HAVING #

统计书籍关联到collection_id=2的次数，筛选次数为0的书籍：

SELECT b.*
FROM db.books b
LEFT JOIN db.collections_has_books chb ON chb.book_id = b.id
GROUP BY b.id
HAVING SUM(CASE WHEN chb.collection_id = 2 THEN 1 ELSE 0 END) = 0;

正确结果 #

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