Charles Kincaid (10/7/2009)
Not exactly. The UNION / UNION ALL is kind of tough one to get wrapped around. UNION ALL combines two or more identically structured result sets into one. All rows from each result set are returned in the output. By contrast UNION (without the ALL) returns only distinct rows.
Example: Say you were doing income tax preparation and had a table of clients for each year. CLIENT2007, CLIENT2008, CLIENT2009. Each table contains just name and taxpayer ID (SSN). Then your old buddy 'Joe Smith' '999-00-1234' came in all three years. Doing the UNION he would show up just the once but UNION ALL he wold appear three times.
Terrible design that but I'm just trying to get over the one point.
Hi Charles, I assume that you are referring to my post earlier responding to SQL Noob.
You might note that I was not pointing out any differences between UNION and UNION ALL, which I am quite aware of the difference of, but if you could replicate the effects of a UNION ALL in a JOIN and whether or not that might be more efficient.
Perhaps my use of the word sets instead of tables made it unclear, but I use that since sometimes the data being unioned in is not always a table but could be a subquery which may include data from multiple tables, hashes, xml or constant values and are not technically identically structured. That's probably just semantics because the result is obviously homogenous.
Anyway, it's still good for people to know that UNION ALL gets all data rows and UNION gets all distinct rows. I've definitely been burned on that one in the past.