SQL Help: finding rows in one table that aren’t in the second table, based on values ​​in two columns

Question

I have two tables. I am trying to find rows in one table that do not exist in the second table based on values ​​in two columns. (I simplified the tables to include only two columns). There are no primary / foreign keys between the two tables. It seems pretty simple, but I now have a brain block!

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 ) 

- create sample data

 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 ' 

- My unsuccessful attempts. I expect John the blacksmith to return.

 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

How about this:

 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 

In my case, I believe only John Smith.

Basically, you perform an outer join between tables of common fields - those rows that are present in both cases will have values ​​for both t1 and t2 .

Those rows that are present only in t1 will not have values ​​for the second table t2 .

Answer 2

I think this should work:

 SELECT t.* FROM Table_1 AS t LEFT JOIN Table_2 t2 ON (t.firstname = t2.firstname AND t.lastname = t2.lastname) WHERE t2.firstname IS NULL AND t2.lastname IS NULL 

But I am surprised that your first attempt did not work:

 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) 

Answer 3

You can try the left outer join using both columns in the ON clause.

Then use the WHERE clause to filter out everything where both tables match

 SELECT * FROM table_1 AS one LEFT OUTER JOIN table_2 AS two ON one.firstname = two.firstname AND one.lastname = two.lastname WHERE two.firstname IS NULL AND two.lastname IS NULL 

Answer 4

External joins are good, but I find this method usually runs faster with large amounts of data.

 SELECT * FROM Table_1 AS t1 WHERE NOT EXISTS ( SELECT * FROM Table_2 AS t2 WHERE t1.firstname = t2.firstname AND t1.lastname = t2.lastname )