选择一个不存在于其他表中的值

NOT IN :

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

不过,同时我更喜欢 NOT EXISTS :

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

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

对于第一个问题,至少有三种常用方法可供选择:

• 不存在
• 不在

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

根据所使用的数据库,每个数据库的性能可能会有所不同。为了 SQL Server (不可为空的列):

NOT EXISTS和NOT IN谓词是搜索缺失值的最佳方法,只要两个相关列都不为NULL。

在您的情况下,这将选择4

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

这将删除它们

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

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

或

select ID from A except select ID from B;

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

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

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)