db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Question on EXCEPT ALL and INTERSECT ALL
Date Thu, 21 Sep 2006 07:32:20 GMT
Rajesh Kartha <kartha02@gmail.com> 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


> (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

Knut Anders

View raw message