Rajesh Kartha <[EMAIL PROTECTED]> writes: > Hello, > Based on the DERBY docs at: > > http://db.apache.org/derby/docs/dev/ref/rrefsqlj21571.html > > adding an ALL to UNION/EXCEPT/INTERSECT gets duplicate rows and the > default is DISTINCT. > > I noticed the rows returned were different between
[snip] > (See below for the full statement execution). > > I am not sure on the above, can someone please share their thoughts/comments > on the expected behaviour. I couldn't see anything wrong in the results returned from your example queries. Duplicate elimination on the result is not the only difference between DISTINCT and ALL. With DISTINCT, UNION/EXCEPT/INTERSECT are set operations. With ALL, they are bag operations. The docs also say this about the ALL keyword: ,------------------- | Depending on which operation is specified, if the number of copies | of a row in the left table is L and the number of copies of that row | in the right table is R, then the number of duplicates of that | particular row that the output table contains (assuming the ALL | keyword is specified) is: | | * UNION: ( L + R ). | * EXCEPT: the maximum of ( L - R ) and 0 (zero). | * INTERSECT: the minimum of L and R. If you have two tables: A(1,1,2,3,3,4,5,5) and B(1,2,3,3,4), you should get these results: A EXCEPT B: (5) A EXCEPT ALL B: (1,5,5) Note that A EXCEPT B is not simply A EXCEPT ALL B with the duplicates removed since there are more 1's in A than in B. > Also I think it would be good to update the docs with > some examples of using EXCEPT and INTERSECT. I think that's a good idea. Clearing up the wording would also be good. -- Knut Anders
