[thelist] MySQL Query problem: unique vals from two fields

[Joe Ngo]

The effect of UNION is similar to performing a UNION ALL and a
DISTINCT operation. I think this is standard behavior not just for
MySQL. Other enterprise databases DB2, MSSQL, Oracle have this
behavior as well

On 11/23/06, Pringle, Ron <RPringle at aurora-il.org> wrote:
> On 11/22/06, Pringle, Ron <RPringle at aurora-il.org> wrote:
> > To close this out, turns out you don't need to use DISTINCT in the
> > query. The default behavior of UNION in MySQL is to remove duplicate
> > rows anyhow. So the final query now looks like:
>
> To clarify, you are saying that for a unioned set A and B, where A
> and/or B have duplicates within its own set, the union operation will
> remove such duplicates? So, it must combine sets and then perform the
> un-dupe over the entire set, rather than take set A and only append
> from set B if the item in set B is not found in A.
>
> Did you do any tests to make sure the DB does not use a different
> algorithm when it knows the two sets have no dupes already?
>
> --
> Matt
>
>
>
> Matt-
>
> No, I didn't. My reading of it is that it combines all data from both
> tables and then removes any duplicates. But I see what you're saying
> regarding using distinct on the individual selects versus using distinct
> on the union. My dataset in this case is so incredibly small that I
> don't know that I could see any differences in the two methods, but it'd
> be interesting to know for future reference. I don't know how you'd go
> about testing it.
>
> Here is the text from the MySQL site:
>
> "The default behavior for UNION is that duplicate rows are removed from
> the result. The optional DISTINCT keyword has no effect other than the
> default because it also specifies duplicate-row removal. With the
> optional ALL keyword, duplicate-row removal does not occur and the
> result includes all matching rows from all the SELECT statements."
>
> http://dev.mysql.com/doc/refman/5.0/en/union.html
>
> Ron
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


-- 
http://xtrahot.chili-mango.net
There's still no place like ~