title: SQL help: find rows in one table which don't exist in second table based on values in two columns
body: <p>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!</p>
  
  <pre><code>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
  ) 
  </code></pre>
  
  <p>-- create sample data</p>
  
  <pre><code>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       '
  </code></pre>
  
  <p>--My failed attempts. I am expecting John smith to return</p>
  
  <pre><code>SELECT t.* FROM Table_1 AS t
  WHERE NOT EXISTS
  (SELECT t2.* FROM Table_2 AS t2
  WHERE t.firstname &lt;&gt; t2.firstname
  AND t.lastname &lt;&gt; t2.lastname)
  
  SELECT * FROM Table_1 AS t
  JOIN Table_2 AS t2
  ON t.firstname &lt;&gt; t2.firstname
  AND t.lastname &lt;&gt; t2.lastname
  </code></pre>