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

Question

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

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 failed attempts. I am expecting John smith 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 get only John Smith back.

You basically do an outer join between the tables on the common fields - those rows that are present in both cases will have values for both t1 and t2.

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

Answer 2

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

Then use the WHERE clause to filter out anything 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 3

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'm surprised that your first try didn't 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 4

Hi there,

If you are using SQL Server, then you might want to give a try to Volpet's Table Diff:

http://www.volpet.com/

You can try a fully-functional copy for 30 days.