SQL Optimization: Union vs. Union All

Everyone should learn the difference between Union and Union All. Knowing it will make you a better programmer, and it's fairly trivial to understand.

SELECT * FROM apples
UNION
SELECT * FROM oranges

When you know for a fact that there will never be any common rows between the apples table and the oranges table, this query will be slightly faster with at low cardinality, and incredibly faster at high cardinality by using "UNION ALL"

SELECT * FROM apples
UNION ALL
SELECT * FROM oranges

The difference between the two queries is this: UNION ALL will simply concatenate the two queries together into the resultset. Just using UNION will concatenate, but then remove duplicates (do a distinct sort). Leaving out this second step can vastly reduce the time it takes for your query to run.