SQL 如何从一个表中选择另一表中不存在的所有记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2686254/
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
How to select all records from one table that do not exist in another table?
提问by z-boss
table1 (id, name)
table2 (id, name)
table1 (id, name)
table2 (id, name)
Query:
询问：
SELECT name
FROM table2
-- that are not in table1 already
回答by Kris
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL
Q: What is happening here?
问：这里发生了什么？
A: Conceptually, we select all rows from table1and for each row we attempt to find a row in table2with the same value for the namecolumn. If there is no such row, we just leave the table2portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the namecolumn (the one we are sure that exists, from table1).
答：从概念上讲，我们从table1每一行中选择所有行，并尝试在其中找到table2具有相同name列值的行。如果没有这样的行，我们只将table2结果的那部分留空。然后我们通过只选择结果中不存在匹配行的那些行来限制我们的选择。最后，我们忽略结果中除了name列（我们确定存在的那个， from table1）之外的所有字段。
While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL
虽然它可能不是所有情况下性能最高的方法，但它应该适用于任何尝试实现ANSI 92 SQL 的数据库引擎
回答by froadie
You can either do
你可以这样做
SELECT name
FROM table2
WHERE name NOT IN
(SELECT name
FROM table1)
or
或者
SELECT name
FROM table2
WHERE NOT EXISTS
(SELECT *
FROM table1
WHERE table1.name = table2.name)
See this questionfor 3 techniques to accomplish this
请参阅此问题以了解实现此目的的 3 种技术
回答by Tan Rezaei
I don't have enough rep points to vote up the 2nd answer. But I have to disagree with the comments on the top answer. The second answer:
我没有足够的代表点数来投票支持第二个答案。但我不得不不同意最高答案的评论。第二个答案：
SELECT name
FROM table2
WHERE name NOT IN
(SELECT name
FROM table1)
Is FAR more efficient in practice. I don't know why, but I'm running it against 800k+ records and the difference is tremendous with the advantage given to the 2nd answer posted above. Just my $0.02
FAR 在实践中是否更有效。我不知道为什么，但我正在针对 800k+ 记录运行它，并且差异是巨大的，因为上面发布的第二个答案具有优势。只是我的 0.02 美元
回答by Winter
This is pure set theory which you can achieve with the minusoperation.
这是您可以通过minus操作实现的纯集合论。
select id, name from table1
minus
select id, name from table2
回答by Anuraj
SELECT <column_list>
FROM TABLEA a
LEFTJOIN TABLEB b
ON a.Key = b.Key
WHERE b.Key IS NULL;
https://www.cloudways.com/blog/how-to-join-two-tables-mysql/
https://www.cloudways.com/blog/how-to-join-two-tables-mysql/
回答by user4872693
Watch out for pitfalls. If the field Namein Table1contain Nulls you are in for surprises.
Better is:
注意陷阱。如果该字段Name中Table1包含空值你是在惊喜。更好的是：
SELECT name
FROM table2
WHERE name NOT IN
(SELECT ISNULL(name ,'')
FROM table1)
回答by Bob
Here's what worked best for me.
这是最适合我的方法。
SELECT *
FROM @T1
EXCEPT
SELECT a.*
FROM @T1 a
JOIN @T2 b ON a.ID = b.ID
This was more than twice as fast as any other method I tried.
这比我尝试过的任何其他方法快两倍多。
回答by Izzy
You can use EXCEPTin mssql or MINUSin oracle, they are identical according to :
您可以EXCEPT在 mssql 或MINUSoracle 中使用，它们是相同的：
回答by David Fawzy
That work sharp for me
这对我来说很重要
SELECT *
FROM [dbo].[table1] t1
LEFT JOIN [dbo].[table2] t2 ON t1.[t1_ID] = t2.[t2_ID]
WHERE t2.[t2_ID] IS NULL
回答by jawahar
See query:
见查询：
SELECT * FROM Table1 WHERE
id NOT IN (SELECT
e.id
FROM
Table1 e
INNER JOIN
Table2 s ON e.id = s.id);
Conceptually would be: Fetching the matching records in subquery and then in main query fetching the records which are not in subquery.
从概念上讲是：在子查询中获取匹配的记录，然后在主查询中获取不在子查询中的记录。
