mysql - 选择在另一个表中没有匹配项目的项目

Question

                          Tables _________________________       _________________________|__________items__________|     |_______readStatus________||___itemId___|____data____|     |___itemId___|___status___|| 1          | cats       |     | 1          | 1          || 2          | dogs       |     | 2          | 1          || 3          | fish       |     |            |            | -------------------------       -------------------------

I have two MySQL tables similar to like shown above. I need to get entries from the item table that don't have a corresponding status 1 in the readStatus table. So in this example I need the entry where data is fish. I'm not very familiar with SQL so I'm not exactly sure how to get about this but based on other questions I've come up with this:

SELECT * 
FROM items
INNER JOIN
readStatus
ON  items.itemId = readStatus.itemId
WHERE readStatus.status != 1

但这不起作用，因为它会跳过 items 表中的任何条目，这些条目在 readStatus 表中没有匹配的条目。添加 status 0 条目来搜索不是一个选项，因为它最终会创建数百万个条目。看起来，在 phpMyAdmin 中，它将条目合并到一个输出中，我并不真正想要，但这不是一个破坏因素。

感谢任何帮助。

Answer 1

使用 LEFT JOIN 而不是 INNER JOIN:

SELECT * 
FROM items
LEFT JOIN   //left join
readStatus
ON  items.itemId = readStatus.itemId
WHERE (readStatus.status != 1 OR readStatus.status IS NULL);

阅读LEFT JOIN教程。