2

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.

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
Patrick
  • 864
  • 1
  • 14
  • 27

3 Answers3

8

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.

mawburn
  • 2,232
  • 4
  • 29
  • 48
Hart CO
  • 34,064
  • 6
  • 48
  • 63
6

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)
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
5

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

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197