Exclude results from a MySQL UNION query

Question

What is the proper way to exclude results from a MySQL UNION? I'm looking for the equivalent to:

(query1)
UNION
(query2)
UNION
(query3)
EXCEPT
(query4)

Answer 1

You can use WHERE NOT IN.

Answer 2

I know this is mySQL, however maybe this might work. I use this on SQLServer

select * from (

select (query1)
union
select (query2)
) a

where a.column = xyz

Answer 3

SELECT a.* FROM 
(
   SELECT ... FROM table_a
   UNION
   SELECT ... FROM table_b
)a 
WHERE a.x NOT IN (...) 
// or   WHERE NOT EXIST  ....' 
// or   LEFT JOIN table_n ON () WHERE table_n.id IS NULL

Answer 4

Can the column(s) being compared for exclusion be nullable?

Meaning, can the values be NULL?

If yes - use either NOT IN or NOT EXISTS - either of these will perform better than LEFT JOIN/IS NULL, read this for details.

If no, use a LEFT JOIN/IS NULL because it's more efficient - see this article for details.