title: Check if a value exists in multiple tables SQL Server
body: <p>I have a list of last names and their unique id's. In our database, we have two tables relating to last names. the first table has current last name, and the second table has alias/past last names. How do I return only the last name and id of individuals where the last name does not exist in both tables?</p>
  
  <p>I've tried using <code>NOT IN:</code></p>
  
  <pre><code>SELECT 
      A.[ID], A.[Last Name]
  FROM 
      Involvement_Data A
  INNER JOIN 
      DIM_CONSTITUENT B ON A.[ID] = B.[LOOKUPID]
  LEFT JOIN 
      DIM_CONSTITUENTALIAS C ON B.[CONSTITUENTSYSTEMID] = C.[CONSTITUENTSYSTEMID]
  WHERE 
      A.[Last Name] NOT IN (B.[LASTNAME], C.[LASTNAME]);
  </code></pre>
  
  <p>But the tables seem to get evaluated individually, causing me to get values back because it didn't exist in <code>DIM_CONSTITUENT</code> OR <code>DIM_CONSTITUENTALIAS</code>. It doesn't evaluate the tables together as a whole. I've also tried using <code>NOT EXISTS</code> with a correlated subquery, but seem to still get results I'm not looking for.</p>