UNION vs. UNION ALL

One very common mistake that people make is in using the union operator. For reasons that are entirely non-obvious to someone new to the language, the union operator automatically applies distinctness to the result. That is all duplicate results are removed. Obviously this is entirely unexpected behaviour.

If you want to get all results, including duplicates, use UNION ALL instead. This does not apply distinctness.

Why would you care?

Well for one thing, by applying distinctness to the resultset a significant performance hit is created. The server has to create internal temporary tables to look at the entire result before sending it to the caller.

Anyways, in general, you want to use UNION ALL instead of UNION 99% of the time. Pretty much the only time you should use UNION instead is if you were going to use SELECT DISTINCT in the queries. Then, if you use UNION you do not need to use SELECT DISTICT and duplicate values from the whole resultset will be removed.