MySQL SQL：选择表A中不在表B中的所有唯一值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4186242/
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
SQL: select all unique values in table A which are not in table B
提问by an0neemus
I have table A
我有表A
Id | Name | Department
-----------------------------
0 | Alice | 1
0 | Alice | 2
1 | Bob | 1
and table B
和表 B
Id | Name
-------------
0 | Alice
I want to select all unique Ids in table A which do not exist in table B. how can I do this?
我想选择表 A 中所有在表 B 中不存在的唯一 ID。我该怎么做？
回答by RedFilter
select distinct id
from TableA a
where not exists (
select id
from TableB
where id = a.id
)
回答by Runonthespot
The most efficient answer is to use a left join, as using "NOT IN" can sometimes prevent a query from using an index, if present.
最有效的答案是使用左连接，因为使用“NOT IN”有时会阻止查询使用索引（如果存在）。
The answer in this case would be something like
在这种情况下，答案将类似于
SELECT DISTINCT
*
FROM
TableA a
LEFT JOIN
TableB b
ON
a.Id = b.Id
WHERE
b.Id IS NULL
Alternatively, this is more readable than a left join, and more efficient than the NOT IN solutions
或者，这比左连接更具可读性，并且比 NOT IN 解决方案更有效
SELECT * FROM TableA a where NOT EXISTS (SELECT * FROM TableB where Id = a.Id)
回答by Vincent Savard
Just to provide a different solution than NOT IN :
只是为了提供与 NOT IN 不同的解决方案：
SELECT DISTINCT A.Id
FROM A
LEFT OUTER JOIN B
ON A.Id = B.Id
WHERE B.Id IS NULL
The "good" solution is usually MINUS or EXCEPT, but MySQL doesn't support it.
“好的”解决方案通常是 MINUS 或 EXCEPT，但 MySQL 不支持它。
This question was asked a few time ago and someone posted an article comparing NOT IN, NOT EXISTS and LEFT OUTER JOIN ... IS NULL. It would be interesting if someone could find it again!
前几天有人问过这个问题，有人发布了一篇比较 NOT IN、NOT EXISTS 和 LEFT OUTER JOIN ... IS NULL 的文章。如果有人能再次找到它会很有趣！
回答by Danny T.
I'd use a NOT EXISTS Like this:
我会使用 NOT EXISTS 像这样：
SELECT A.Id
FROM TableA A
WHERE NOT EXISTS (SELECT B.Id FROM TableB B WHERE A.Id = B.Id)
GROUP BY A.Id
回答by FrustratedWithFormsDesigner
The subquery will get all the IDs in B. The group by...havingwill get all the unique IDs in A that are also not in B.
子查询将获得 B 中的所有 ID。group by...having将获得 A 中所有不在 B 中的唯一 ID。
select *
from A
where id not in
(
select distinct id
from B
)
group by ID
having count(*) > 1;
回答by Vladimir Ivanov
SELECT DISTINCT Id FROM A WHERE Id NOT IN (SELECT ID FROM B);
回答by Cfreak
SELECT DISTINCT Id FROM A WHERE Id NOT IN(SELECT DISTINCT Id FROM B);
