db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng" <yipng...@gmail.com>
Subject Re: Question on EXCEPT ALL and INTERSECT ALL
Date Thu, 21 Sep 2006 07:46:56 GMT
On 9/20/06, Rajesh Kartha <kartha02@gmail.com> wrote:<snip>

> ij> --
> --  I expected 6,6,8,9 only
> --
> (select i from t1 union all select j from t2) except all (select k from
> t3);
> 1
> -----------
> 1
> 1
> 2
> 3
> 6
> 6
> 8
> 9
>
> 8 rows selected
> </snip>


 Hi Rajesh:

  The query result above returned from the system is correct.  Let's take
the query apart so we can visualize better:

Q1:  (select i from t1 union all select j from t2)
Q2:  select k from t3

Q1 will retain all rows from both table t1 and t2, so the result is:
1,2,3,4,9,1,3,2,1,6,8,6
Q2 result is:  5,2,3,4,1

Q1 EXCEPT ALL Q2, will have the result:  1, 1, 2, 3, 6, 6, 8, 9 because Q1
have three rows containing the value of 1, Q2 has only one, so the result
should retain (3-1 or 2) rows containing the value of 1.  Q1 have two rows
containing the value of 2, Q2 has only one, so
the result should retain (2-1 or 1) row containing the value of 2 and so
forth.  Rows of Q1 that does not appear in Q2 will be retained in the final
result as well.

Q1 EXCEPT Q2, will have the result:  6,8,9 because duplicates are eliminated
from both
Q1 and Q2 before applying the EXCEPT operator:

Q1-nodups:  1,2,3,4,6,8,9
Q2-nodups:  1,2,3,4,5

So the result here is 6,8,9, which is also correct in your output.  Now,
let's try the INTERSECT testcase you listed, the output is also expected and
correct:
<snip>
ij> --
-- Are these Wrong results, I expected 1,1,1,2,2,3,3,4
--
(select i from t1 union all select j from t2) intersect all ( select k from
t3);

1
-----------
1
2
3
4

4 rows selected
</snip>

Again, using the results from above:

Q1:  1,2,3,4,9,1,3,2,1,6,8,6
Q2 : 5,2,3,4,1

Q1 INTERSECT ALL Q2 means "return all rows from Q1 that are also found in
Q2".  The result will be 1,2,3,4 because there are three rows containing the
value of 1 in Q1 but Q2 only has one, so the result will only have one row
containing the value of 1.  There are two rows containing the value of 2 in
Q1 but Q2 only has one, so the result will only  have one row containing the
value of 2 and so forth.  (If Q2 happens to have 2 rows containing the value
of 2, the final result will have 2 rows containing the value of 2). Rows
that only appear in one of the two queries will not make it into the final
result.

For Q1 INTERSECT Q2, Q1 and Q2 will first have their duplicates removed
before
applying the INTERSECT operator:

Q1-nodups:  1,2,3,4,6,8,9
Q2-nodups:  1,2,3,4,5

So, the result is again 1,2,3,4.  Hope this helps.

Yip

Mime
View raw message