{"id":555,"date":"2012-04-09T16:11:47","date_gmt":"2012-04-09T16:11:47","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=555"},"modified":"2012-04-09T16:13:05","modified_gmt":"2012-04-09T16:13:05","slug":"mysql-union-unexpected-behavior","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/mysql-union-unexpected-behavior\/","title":{"rendered":"MySQL Union All – Unexpected behavior"},"content":{"rendered":"

I don't use the UNION ALL SQL keyword all that often. In most cases, I'm happy with UNION<\/strong>, which merges duplicate rows.<\/p>\n

Today, for this first time, I found a situation which required more than one UNION ALL <\/strong>keyword in a query. For those not familiar, UNION ALL<\/strong> is very similar to UNION<\/strong>, except that it does not merge duplicate rows.<\/p>\n

My original query looked a little something like:<\/p>\n

SELECT blah
\n\tFROM haha
\n\tWHERE blah = 'blah'<\/code><\/p>\n

\t UNION ALL<\/code><\/p>\n

\tSELECT blah
\n\tFROM hehe
\n\tWHERE blah = 'blah'<\/code><\/p>\n

\tUNION<\/code>
\n\t
\n\tSELECT blah
\n\tFROM hoho
\n\tWHERE blah = 'blah'<\/code><\/p>\n

 <\/p>\n

Here's the unexpected part – UNION ALL<\/strong> actually merged the duplicate rows from table haha <\/em>and table hehe<\/em>. It appears that if any UNION <\/strong>keyword exists in a given query or subquery, it forces all UNION ALL<\/strong> keywords to behave like a plain old UNION<\/strong>.<\/p>\n

I'm open to criticism here, this seems like odd behavior and I have the feeling I missed something in the SQL docs.<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[77,13],"tags":[165],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/555"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=555"}],"version-history":[{"count":2,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/555\/revisions"}],"predecessor-version":[{"id":557,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/555\/revisions\/557"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}