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
