SQL帮助: find rows in one table which don't exist in second table based on values in two columns

Question

我有两张 table 。我试图根据两列中的值在一个表中查找第二个表中不存在的行。 (我已将表格简化为仅包含两列)。两个表之间没有主键/外键。看起来很简单，但我现在脑子有问题!

DDL:
CREATE TABLE [dbo].[Table_1](
    [firstname] [nchar](10) NULL,
    [lastname] [nchar](10) NULL
) 

CREATE TABLE [dbo].[Table_2](
    [firstname] [nchar](10) NULL,
    [lastname] [nchar](10) NULL
) 

-- 创建样本数据

INSERT INTO [dbo].[Table_1]([firstname], [lastname])
SELECT N'John      ', N'Doe       ' UNION ALL
SELECT N'John      ', N'Smith     '
INSERT INTO [dbo].[Table_2]([firstname], [lastname])
SELECT N'John      ', N'Doe       '

——我失败的尝试。我期待约翰史密斯回来

SELECT t.* FROM Table_1 AS t
WHERE NOT EXISTS
(SELECT t2.* FROM Table_2 AS t2
WHERE t.firstname <> t2.firstname
AND t.lastname <> t2.lastname)

SELECT * FROM Table_1 AS t
JOIN Table_2 AS t2
ON t.firstname <> t2.firstname
AND t.lastname <> t2.lastname

Answer 1

这个怎么样:

SELECT * 
FROM Table_1 AS t1
LEFT OUTER JOIN Table_2 AS t2
ON t1.firstname = t2.firstname
AND t1.lastname = t2.lastname
WHERE t2.firstname IS NULL AND t2.lastname IS NULL

就我而言，我只得到了约翰史密斯。

你基本上做一个 外连接 在公共(public)字段的表之间 - 在这两种情况下都存在的那些行将具有两个 t1 的值和 t2 .

这些行只存在于 t1第二个表 t2 将没有任何值.