UNION ALL and NOT IN together

sql, sql-server, sql-server-2008, union

Solution

The parts of a `UNION` are handled as separate queries, so you can group them in a subquery:

SELECT Name 
FROM (Select Name from Fname
      UNION ALL
      Select Name from Lname)sub
WHERE Name NOT IN (Select Name from Exceptions)

You can keep that as `UNION ALL` if you don't care about duplicates.

Problem

SQL Server - I have 3 simple tables (Fname, Lname and Exceptions) with one column each called Name. I want my end result to look like: (Everybody in Fname + Everybody in LName) - (Everybody in Exceptions). FName: ``` Name A B ``` LName: ``` Name Y Z ``` Exceptions: ``` Name A Z ``` Expected Query Result Set: ``` B Y ``` Current SQL Query: ``` Select Name from Fname UNION ALL Select Name from Lname WHERE Name NOT IN (Select Name from Exceptions) ``` The SQL query only works on removing data which appears in LName but not in Fname. Can somebody please help.

Original source