SQL 选择另一个表中不存在的值

select a value where it doesn't exist in another table

提问by Wai Wong

I have two tables

我有两张桌子

Table A:

表一：

ID
1
2
3
4

Table B:

表 B：

ID
1
2
3

I have two requests:

我有两个要求：

• I want to select all rows in table A that table B doesn't have, which in this case is row 4.
• I want to delete all rows that table B doesn't have.

• 我想选择表 A 中表 B 没有的所有行，在这种情况下是第 4 行。
• 我想删除表 B 没有的所有行。

I am using SQL Server 2000.

我正在使用 SQL Server 2000。

回答by Tim Schmelter

You could use NOT IN:

你可以使用NOT IN：

SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B)

However, meanwhile i prefer NOT EXISTS:

但是，同时我更喜欢NOT EXISTS：

SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID)

There are other options as well, this article explains all advantages and disadvantages very well:

还有其他选择，本文很好地解释了所有优点和缺点：

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

我应该使用 NOT IN、OUTER APPLY、LEFT OUTER JOIN、EXCEPT 还是 NOT EXISTS？

回答by Mark Byers

For your first question there are at least three common methods to choose from:

对于您的第一个问题，至少有三种常用方法可供选择：

• NOT EXISTS
• NOT IN
• LEFT JOIN

• 不存在
• 不在
• 左加入

The SQL looks like this:

SQL 看起来像这样：

SELECT * FROM TableA WHERE NOT EXISTS (
    SELECT NULL
    FROM TableB
    WHERE TableB.ID = TableA.ID
)

SELECT * FROM TableA WHERE ID NOT IN (
    SELECT ID FROM TableB
)

SELECT TableA.* FROM TableA 
LEFT JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableB.ID IS NULL

Depending on which database you are using, the performance of each can vary. For SQL Server(not nullable columns):

根据您使用的数据库，每个数据库的性能可能会有所不同。对于SQL Server（不可为空的列）：

NOT EXISTS and NOT IN predicates are the best way to search for missing values, as long as both columns in question are NOT NULL.

NOT EXISTS 和 NOT IN 谓词是搜索缺失值的最佳方式，只要有问题的两列都是 NOT NULL。

回答by kamasheto

This would select 4 in your case

这将在您的情况下选择 4

SELECT ID FROM TableA WHERE ID NOT IN (SELECT ID FROM TableB)

This would delete them

这将删除它们

DELETE FROM TableA WHERE ID NOT IN (SELECT ID FROM TableB)

回答by Mark Baker

SELECT ID 
  FROM A 
 WHERE NOT EXISTS( SELECT 1
                     FROM B
                    WHERE B.ID = A.ID
                 )

回答by Behrang Saeedzadeh

select ID from A where ID not in (select ID from B);

or

或者

select ID from A except select ID from B;

Your second question:

你的第二个问题：

delete from A where ID not in (select ID from B);

回答by Neil Knight

SELECT ID
  FROM A
 WHERE ID NOT IN (
      SELECT ID
        FROM B);

---

---

SELECT ID    
  FROM A a
 WHERE NOT EXISTS (
      SELECT 1 
        FROM B b
       WHERE b.ID = a.ID)

---

---

         SELECT a.ID 
           FROM A a    
LEFT OUTER JOIN B b 
             ON a.ID = b.ID    
          WHERE b.ID IS NULL

---

---

DELETE 
  FROM A 
 WHERE ID NOT IN (
      SELECT ID 
        FROM B)