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

Table1 (id, name) Table2 (id, name)

查询:

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

当前回答

那对我来说很有用

SELECT * 
FROM [dbo].[table1] t1
LEFT JOIN [dbo].[table2] t2 ON t1.[t1_ID] = t2.[t2_ID]
WHERE t2.[t2_ID] IS NULL

其他回答

首先定义表的别名，如t1和t2。然后得到第二个表的记录。然后使用where条件匹配记录:

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

看到查询:

SELECT * FROM Table1 WHERE
id NOT IN (SELECT 
        e.id
    FROM
        Table1 e
            INNER JOIN
        Table2 s ON e.id = s.id);

从概念上讲是:在子查询中获取匹配的记录，然后在主查询中获取不在子查询中的记录。

你可以在mssql中使用EXCEPT或在oracle中使用MINUS，它们是相同的:

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

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/

小心陷阱。如果表1中的字段Name包含null，你就会感到惊讶。更好的是:

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

推荐文章