Weird SQL UNION Results

Published: September 26, 2007

Hey, Ted:

I was intrigued by your article Have Your Cake and Eat It, Too. I decided to try your tip with some of my own data. The resulting data set had an anomaly in it. The system omitted a row of input, yet the total was still correct. Why?

--Wyatt

It's my bad, Wyatt, and I should have known better. (Note to self: Leave instructions for my survivors to put those last five words on my tombstone. It seems to accurately describe my life so far! ;)

The problem was that the example used the word UNION, but it should have used two words--UNION ALL. In my example, UNION gave me the right results. It didn't occur to me that UNION ALL was the proper solution.

First, allow me to give a little info about unions for the benefit of those who are not familiar with them. Unions combine two data sets into one. The intended purpose, according to set theory, is to combine two subsets of one superset. (See Thinking in Sets for an example of this usage.) But I seem to find unions more helpful in situations where two differing sets are being combined, as in the article you cited.

The reason the system omitted a row from your query, Wyatt, is that you had two identical rows in your input data. Yet the summary figures were correct because summary queries do not eliminate duplicate rows. Therefore you got something like this.

Name          Amt
-------   -------    
Jones       25.00
Smith       10.00
ZZ          45.00

So here's the rule of thumb that I forgot about when I wrote that tip. When you union two select queries, consider whether you are combining like data or unlike data. If you are combining like data (e.g., customers and customers) you probably want UNION. If you are combining unlike data (e.g., customers and vendors, or as in the referenced "Cake" article, customer details and summary totals), you probably want UNION ALL. My experience is that you will probably use UNION ALL more than UNION.

This rule of thumb does not always hold true, but you and the other readers of this publication are smart enough to determine when to go against the rule.

--Ted

RELATED STORIES

Have Your Cake and Eat It, Too

Thinking in Sets

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot