Table1 (id, name) Table2 (id, name)
查询:
SELECT name
FROM table2
-- that are not in table1 already
Table1 (id, name) Table2 (id, name)
查询:
SELECT name
FROM table2
-- that are not in table1 already
当前回答
以下是对我最有效的方法。
SELECT *
FROM @T1
EXCEPT
SELECT a.*
FROM @T1 a
JOIN @T2 b ON a.ID = b.ID
这比我试过的其他方法快了一倍多。
其他回答
那对我来说很有用
SELECT *
FROM [dbo].[table1] t1
LEFT JOIN [dbo].[table2] t2 ON t1.[t1_ID] = t2.[t2_ID]
WHERE t2.[t2_ID] IS NULL
你可以在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/
首先定义表的别名,如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);
从概念上讲是:在子查询中获取匹配的记录,然后在主查询中获取不在子查询中的记录。
小心陷阱。如果表1中的字段Name包含null,你就会感到惊讶。 更好的是:
SELECT name
FROM table2
WHERE name NOT IN
(SELECT ISNULL(name ,'')
FROM table1)