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

Solution 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.

Solution 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'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)

Solution 3

Outer joins are fine but I find this method typically executes faster on 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
    )