如何从另一个表中不存在的表中选择所有记录?

table1 (id, name)
table2 (id, name)

表1 (id、name)表2 (id、name)

Query:

查询:

SELECT name   
FROM table2  
-- that are not in table1 already

8 个解决方案

#1

529

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 table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion 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 name column (the one we are sure that exists, from table1).

A:从概念上说，我们从table1中选择所有的行，对于每一行，我们尝试在table2中找到与name列相同的值。如果没有这样的行，我们就把表2的部分留给该行。然后，通过仅选择匹配行不存在的结果中的行来约束我们的选择。最后，除了name列之外，我们忽略了结果中的所有字段(我们确信它存在于表1中)。

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。

#2

163

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT name 
     FROM table1)

或

SELECT name 
FROM table2 
WHERE NOT EXISTS 
    (SELECT * 
     FROM table2 
     WHERE table1.name = table2.name)

See this question for 3 techniques to accomplish this

请用3个技巧来完成这个问题。

#3

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

在实践中要有效得多。我也不知道为什么，但是我要用800k+的记录来运行它，而且这个区别是巨大的，因为上面的第2个答案。只是我的0.02美元

#4

This is pure set theory which you can achieve with the minus operation.

这是纯集合理论，你可以用负运算来实现。

select id, name from table1
minus
select id, name from table2

#5

Watch out for pitfalls. If the field Name in Table1 contain Nulls you are in for surprises. Better is:

当心陷阱。如果Table1中的字段名包含了Nulls，那么就会出现意外。更好的是:

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT ISNULL(name ,'')
     FROM table1)

#6

You can use EXCEPT in mssql or MINUS in oracle, they are identical according to :

您可以在oracle中使用mssql或减号，它们是相同的:

http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/

#7

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.

这比我尝试过的其他方法快两倍。

#8

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

#1

529

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL