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

I have two tables

我有两张桌子

Table A:

表A：

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.
我想选择表B中没有的表A中的所有行，在本例中是第4行。
I want to delete all rows that table B doesn't have.
我想删除表B没有的所有行。

I am using SQL Server 2000.

我正在使用SQL Server 2000。

6 个解决方案

#1

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:

但是，同时我不喜欢不存在：

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？

#2

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

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

NOT EXISTS
不存在
NOT IN
不在
LEFT JOIN
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。

#3

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)

#4

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

要么

select ID from A except select ID from B;

Your second question:

你的第二个问题：

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

#5

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

#6

-2

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)

#1