I have a question about the SQL Specifications in regards to Unions...
I recently put together a query that involved unions similar to the following: SELECT 'Query 1' as id, my_value FROM my_view UNION SELECT 'Query 2' as id, my_value FROM my_other_view ; The first query in the union gave me 39 records and the second gave me 34 records. I was expecting the union to give me 39 + 34 = 73 records. When I ran this against DB2, I got 35 records (not sure about PostgreSQL - will have to 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 then added to 35. The reason was, my_value was duplicated in my_view and in my_other_view. What the Union appeared to be doing was to gather the data and then do a group by on the complete results. I expected it to only eliminate duplicates BETWEEN the two queries, not WITHIN the queries. My question, what do the SQL Specifications say should happen on a Union? Is it supposed to eliminate duplicates even WITHIN the individual queries that are being unioned? Thanks! -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==================================================================
