I have a question about the SQL Specifications in regards to Unions...<br /><br />I recently put together a query that
involvedunions similar to the following:<br /><br />SELECT<br /> 'Query 1' as id,<br /> my_value<br />FROM
my_view<br />UNION<br />SELECT<br /> 'Query 2' as id,<br /> my_value<br />FROM my_other_view<br />;<br /><br
/>Thefirst query in the union gave me 39 records and the second gave me 34 records. I was expecting the union to give
me39 + 34 = 73 records. <br /><br />When I ran this against DB2, I got 35 records (not sure about PostgreSQL - will
haveto try it when I get home). What I found was when I did a group by my_value on each query I got two values that
thenadded to 35. The reason was, my_value was duplicated in my_view and in my_other_view. What the Union appeared to
bedoing was to gather the data and then do a group by on the complete results. I expected it to only eliminate
duplicatesBETWEEN the two queries, not WITHIN the queries. <br /><br />My question, what do the SQL Specifications say
shouldhappen on a Union? Is it supposed to eliminate duplicates even WITHIN the individual queries that are being
unioned?<br/><br />Thanks!<br clear="all" /><br />-- <br />
==================================================================<br/> Aaron Bono<br /> Aranya Software
Technologies,Inc.<br /> <a href="http://www.aranya.com">http://www.aranya.com</a><br /> <a
href="http://codeelixir.com">http://codeelixir.com</a><br
/>==================================================================