MySQL select * from table1 不存在于 table2 有条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1598322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select * from table1 that does not exist in table2 with conditional
提问by user192738
I have 2 tables. One is a table with things that can be learned. There is a JID that desribes each kind of row, and is unique to each row. The second table is a log of things that have been learned (the JID) and also the userid for the person that learned it. I am currently using this to select all of the data for the JID, but only the ones the user has learned based on userid.
我有2张桌子。一个是一张桌子，上面放着可以学习的东西。有一个 JID 来描述每一行，并且每一行都是唯一的。第二个表是所学内容的日志（JID）以及学习它的人的用户 ID。我目前正在使用它来选择 JID 的所有数据，但只有用户根据 userid 学到的数据。
SELECT *
FROM tablelist1
LEFT JOIN tablelog2 ON (tablelist1.JID = tablelog2.JID)
AND tablelog2.UID = 'php var'
WHERE tablelog2.JID IS NOT NULL
I now need to select the rows of things to learn, but only the things the userid has NOT already learned. I am obviously very new to this, bear with me. :) I tried using IS NULL, but while it seems it works, it gives duplicate JID's one being NULL, one being correct.
我现在需要选择要学习的内容行，但只需要选择用户 ID 尚未学习的内容。我显然对此很陌生，请耐心等待。:) 我尝试使用 IS NULL，但虽然它似乎有效，但它给出了重复的 JID，一个是 NULL，一个是正确的。
回答by OMG Ponies
Using LEFT JOIN/IS NULL:
使用 LEFT JOIN/IS NULL：
SELECT t.*
FROM TABLE_LIST t
LEFT JOIN TABLE_LOG tl ON tl.jid = t.jid
WHERE tl.jid IS NULL
Using NOT IN:
使用不在：
SELECT t.*
FROM TABLE_LIST t
WHERE t.jid NOT IN (SELECT tl.jid
FROM TABLE_LOG tl
GROUP BY tl.jid)
Using NOT EXISTS:
使用不存在：
SELECT t.*
FROM TABLE_LIST t
WHERE NOT EXISTS(SELECT NULL
FROM TABLE_LOG tl
WHERE tl.jid = t.jid)
FYI
LEFT JOIN/IS NULL and NOT IN are equivalent in MySQL - they will perform the same, while NOT EXISTS is slower/less efficient. For more details: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
仅供参考
LEFT JOIN/IS NULL 和 NOT IN 在 MySQL 中是等效的 - 它们将执行相同的操作，而 NOT EXISTS 速度较慢/效率较低。更多详情：http: //explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
回答by Larry Lustig
First off, you should be using an INNER JOIN on your existing query:
首先，您应该在现有查询中使用 INNER JOIN：
SELECT * FROM tablelist1
INNER JOIN tablelog2 ON (tablelist1.JID = tablelog2.JID)
WHERE tablelog2.UID = 'php var'
The way you're doing it you're getting all the rows from tablelist1, then going to extra trouble to exclude the ones that don't have a match in tablelog2. The INNER JOIN will do that for you, and more efficiently.
您这样做的方式是从 tablelist1 获取所有行，然后要额外麻烦排除那些在 tablelog2 中不匹配的行。INNER JOIN 将为您做到这一点，而且效率更高。
Secondly, to find for user "X" all the learnable-things that the user hasn't learned, do:
其次，要为用户“X”找到用户未学过的所有可学习的东西，请执行以下操作：
SELECT * FROM tablelist1
WHERE NOT EXISTS (SELECT JID FROM tablelog2 WHERE UID = 'X')
