I don't use the UNION ALL SQL keyword all that often. In most cases, I'm happy with UNION, which merges duplicate rows.
Today, for this first time, I found a situation which required more than one UNION ALL keyword in a query. For those not familiar, UNION ALL is very similar to UNION, except that it does not merge duplicate rows.
My original query looked a little something like:
SELECT blah
FROM haha
WHERE blah = 'blah'
UNION ALL
SELECT blah
FROM hehe
WHERE blah = 'blah'
UNION
SELECT blah
FROM hoho
WHERE blah = 'blah'
Here's the unexpected part – UNION ALL actually merged the duplicate rows from table haha and table hehe. It appears that if any UNION keyword exists in a given query or subquery, it forces all UNION ALL keywords to behave like a plain old UNION.
I'm open to criticism here, this seems like odd behavior and I have the feeling I missed something in the SQL docs.