SiteKickr Web Development

MySQL Union All – Unexpected behavior

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.