db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <karth...@gmail.com>
Subject Question on EXCEPT ALL and INTERSECT ALL
Date Thu, 21 Sep 2006 00:35:43 GMT
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

ij>(select i from t1 union all select j from t2) except ( select k from t3);
1
-----------
6
8
9

3 rows selected
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

If the EXCEPT /EXCEPT ALL are used on tables, they seem to work fine:
ij> select * from t1 except select k from t3;
1
-----------
6
8

2 rows selected
ij> select * from t1 except all select k from t3;
1
-----------
6
6
8
--------------------------------------------------------------------------
--------------------------------------------------------------------------
And for INTERSECT, there is no difference in the rows returned with and 
without ALL.
on simple tables  and  when used with UNIONS as well.

ij> select * from t1 intersect select k from t3;
1
-----------
1
2
3

3 rows selected
ij> select * from t1 intersect all select k from t3;
1
-----------
1
2
3

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

Also I think it would be good to update the docs with
some examples of using EXCEPT and INTERSECT.

-Rajesh

====================================================
ij version 10.2
ij> connect 'jdbc:derby:testdb;create=true';
ij> CREATE TABLE t1 (i int);
0 rows inserted/updated/deleted
ij> CREATE TABLE t2 (j int);
0 rows inserted/updated/deleted
ij> CREATE TABLE t3 (k int);
0 rows inserted/updated/deleted
ij> INSERT INTO t1 VALUES 3,2,1,6,8,6;
6 rows inserted/updated/deleted
ij> INSERT INTO t2 VALUES 1,2,3,4,9,1;
6 rows inserted/updated/deleted
ij> INSERT INTO t3 VALUES 5,2,3,4,1;
5 rows inserted/updated/deleted
ij> select i from t1 union all select j from t2;
1
-----------
3
2
1
6
8
6
1
2
3
4
9
1

12 rows selected
ij> select k from t3;
K
-----------
5
2
3
4
1

5 rows selected
ij> --
--Except and Except All
--
(select i from t1 union all select j from t2) except ( select k from t3);
1
-----------
6
8
9

3 rows selected
ij> --
-- Are these Wrong results, 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
ij> --
--Intersect
--
(select i from t1 union all select j from t2) intersect ( select k from t3);
1
-----------
1
2
3
4

4 rows selected
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

Mime
View raw message