UNION ALL and NOT IN together

Question

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.

Solution 1

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.

Solution 2

You need a subquery, i prefer NOT EXISTS:

SELECT X.name 
FROM   (SELECT name 
        FROM   fname 
        UNION ALL 
        SELECT name 
        FROM   lname) X 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   exceptions E 
                   WHERE  x.name = E.name) 

Demo

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

However, NOT IN works the same way:

SELECT X.name 
FROM   (SELECT name 
        FROM   fname 
        UNION ALL 
        SELECT name 
        FROM   lname) X 
WHERE  X.name NOT IN (SELECT name 
                      FROM   exceptions) 

Solution 3

If your RBBMS have except (SQL Server or PostgreSQL have, don't know about MySQL)

select Name from FName
union all
select Name from LName
except
select Name from Exceptions

sql fiddle demo