MySQL左外连接，排除属于用户的第二个表中的项目

Question

我的 MySQL 数据库中有两个表，一个是数据库中所有图书的图书馆，另一个包含与用户图书馆中的图书相对应的各个行。

例如:

图书馆表

 `id`        `title`...
=====        ===========
  1          Moby Dick
  2          Harry Potter

收款表

 `id`        `user`      `book`
=====        ======      =======
  1            1            2
  2            2            2
  3            1            1

我想做的是运行一个查询，该查询将显示用户收藏中不 的所有书籍。我可以运行此查询来显示不在任何用户收藏中的所有书籍:

SELECT * 
FROM  `library` 
LEFT OUTER JOIN  `collection` ON  `library`.`id` =  `collection`.`book` 
WHERE  `collection`.`book` IS NULL

据我所知，这工作得很好。在 PHPMyAdmin 中运行它会导致所有不在收藏表中的书籍。

但是，我如何将其限制为特定用户？例如，对于上述虚拟数据，如果用户 2 运行查询，我希望得到书 1，而如果用户 1 运行查询，则没有书。

仅仅添加一个 AND user=[id] 是行不通的，而且我对 JOIN 语句的了解极其有限，我真的没有取得任何进展。

另外，返回结果的 ID(显示的查询，它不做我想要的但确实起作用)是 0——我如何确保返回的 ID 是 library.id 的 ID ?

Answer 1

您必须将LEFT JOIN 选择范围缩小到只有特定用户拥有的书籍，然后 NULL 中的任何内容连接表将是用户在他/她的收藏中没有的行(书):

SELECT
    a.id,
    a.title
FROM
    library a
LEFT JOIN
    (
        SELECT book
        FROM collection
        WHERE user = <userid>
    ) b ON a.id = b.book
WHERE 
    b.book IS NULL

另一种方法是:

SELECT
    a.id,
    a.title
FROM
    library a
WHERE 
    a.id NOT IN
    (
        SELECT book
        FROM collection
        WHERE user = <userid>
    )

但是，第一个解决方案更为理想，因为 MySQL 将为每一行执行一次 NOT IN 子查询，而不是对整个查询执行一次。直观上，您会期望 MySQL 执行一次子查询并将其用作列表，但 MySQL 不够智能，无法区分相关子查询和非相关子查询。

如前所述here :

"The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery."