ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- create table t1( id integer not null primary key, i1 integer, i2 integer, c10 char(10), c30 char(30), tm time); 0 rows inserted/updated/deleted ij> create table t2( id integer not null primary key, i1 integer, i2 integer, vc20 varchar(20), d double, dt date); 0 rows inserted/updated/deleted ij> insert into t1(id,i1,i2,c10,c30) values (1,1,1,'a','123456789012345678901234567890'), (2,1,2,'a','bb'), (3,1,3,'b','bb'), (4,1,3,'zz','5'), (5,null,null,null,'1.0'), (6,null,null,null,'a'); 6 rows inserted/updated/deleted ij> insert into t2(id,i1,i2,vc20,d) values (1,1,1,'a',1.0), (2,1,2,'a',1.1), (5,null,null,'12345678901234567890',3), (100,1,3,'zz',3), (101,1,2,'bb',null), (102,5,5,'',null), (103,1,3,' a',null), (104,1,3,'null',7.4); 8 rows inserted/updated/deleted ij> -- no duplicates select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by id DESC,i1,i2; ID |I1 |I2 ----------------------------------- 5 |NULL |NULL 2 |1 |2 1 |1 |1 ij> select id,i1,i2 from t1 intersect distinct select id,i1,i2 from t2 order by id DESC,i1,i2; ID |I1 |I2 ----------------------------------- 5 |NULL |NULL 2 |1 |2 1 |1 |1 ij> select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 1,2,3; ID |I1 |I2 ----------------------------------- 1 |1 |1 2 |1 |2 5 |NULL |NULL ij> -- Only specify order by on some columns select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by i2, id DESC; ID |I1 |I2 ----------------------------------- 1 |1 |1 2 |1 |2 5 |NULL |NULL ij> select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 3 DESC, 1; ID |I1 |I2 ----------------------------------- 5 |NULL |NULL 2 |1 |2 1 |1 |1 ij> -- duplicates select i1,i2 from t1 intersect select i1,i2 from t2 order by 1,2; I1 |I2 ----------------------- 1 |1 1 |2 1 |3 NULL |NULL ij> select i1,i2 from t1 intersect distinct select i1,i2 from t2 order by 1,2; I1 |I2 ----------------------- 1 |1 1 |2 1 |3 NULL |NULL ij> select i1,i2 from t1 intersect all select i1,i2 from t2 order by 1,2; I1 |I2 ----------------------- 1 |1 1 |2 1 |3 1 |3 NULL |NULL ij> -- right side is empty select i1,i2 from t1 intersect select i1,i2 from t2 where id = -1; I1 |I2 ----------------------- ij> select i1,i2 from t1 intersect all select i1,i2 from t2 where id = -1; I1 |I2 ----------------------- ij> -- left side is empty select i1,i2 from t1 where id = -1 intersect all select i1,i2 from t2; I1 |I2 ----------------------- ij> -- check precedence select i1,i2 from t1 intersect all select i1,i2 from t2 intersect values(5,5),(1,3) order by 1,2; 1 |2 ----------------------- 1 |3 ij> (select i1,i2 from t1 intersect all select i1,i2 from t2) intersect values(5,5),(1,3) order by 1,2; 1 |2 ----------------------- 1 |3 ij> values(-1,-1,-1) union select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by 1,2,3; 1 |2 |3 ----------------------------------- -1 |-1 |-1 1 |1 |1 2 |1 |2 5 |NULL |NULL ij> select id,i1,i2 from t1 intersect select id,i1,i2 from t2 union values(-1,-1,-1) order by 1,2,3; 1 |2 |3 ----------------------------------- -1 |-1 |-1 1 |1 |1 2 |1 |2 5 |NULL |NULL ij> -- check conversions select c10 from t1 intersect select vc20 from t2 order by 1; 1 -------------------- a zz ij> select c30 from t1 intersect select vc20 from t2; 1 ------------------------------ a bb ij> select c30 from t1 intersect all select vc20 from t2; 1 ------------------------------ a bb ij> -- check insert intersect into table and intersect without order by create table r( i1 integer, i2 integer); 0 rows inserted/updated/deleted ij> insert into r select i1,i2 from t1 intersect select i1,i2 from t2; 4 rows inserted/updated/deleted ij> select i1,i2 from r order by 1,2; I1 |I2 ----------------------- 1 |1 1 |2 1 |3 NULL |NULL ij> delete from r; 4 rows inserted/updated/deleted ij> insert into r select i1,i2 from t1 intersect all select i1,i2 from t2; 5 rows inserted/updated/deleted ij> select i1,i2 from r order by 1,2; I1 |I2 ----------------------- 1 |1 1 |2 1 |3 1 |3 NULL |NULL ij> delete from r; 5 rows inserted/updated/deleted ij> -- test LOB create table t3( i1 integer, cl clob(64), bl blob(1M)); 0 rows inserted/updated/deleted ij> insert into t3 values (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M))); 1 row inserted/updated/deleted ij> create table t4( i1 integer, cl clob(64), bl blob(1M)); 0 rows inserted/updated/deleted ij> insert into t4 values (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M))); 1 row inserted/updated/deleted ij> select cl from t3 intersect select cl from t4 order by 1; ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> select bl from t3 intersect select bl from t4 order by 1; ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- invalid conversion select tm from t1 intersect select dt from t2; ERROR 42X61: Types 'TIME' and 'DATE' are not INTERSECT compatible. ij> select c30 from t1 intersect select d from t2; ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not INTERSECT compatible. ij> -- different number of columns select i1 from t1 intersect select i1,i2 from t2; ERROR 42X58: The number of columns on the left and right sides of the INTERSECT must be the same. ij> -- ? in select list of intersect select ? from t1 intersect select i1 from t2; ERROR 42X34: There is a ? parameter in the select list. This is not allowed. ij> select i1 from t1 intersect select ? from t2; ERROR 42X34: There is a ? parameter in the select list. This is not allowed. ij> -- except tests select id,i1,i2 from t1 except select id,i1,i2 from t2 order by id,i1,i2; ID |I1 |I2 ----------------------------------- 3 |1 |3 4 |1 |3 6 |NULL |NULL ij> select id,i1,i2 from t1 except distinct select id,i1,i2 from t2 order by id,i1,i2; ID |I1 |I2 ----------------------------------- 3 |1 |3 4 |1 |3 6 |NULL |NULL ij> select id,i1,i2 from t1 except all select id,i1,i2 from t2 order by 1 DESC,2,3; ID |I1 |I2 ----------------------------------- 6 |NULL |NULL 4 |1 |3 3 |1 |3 ij> select id,i1,i2 from t2 except select id,i1,i2 from t1 order by 1,2,3; ID |I1 |I2 ----------------------------------- 100 |1 |3 101 |1 |2 102 |5 |5 103 |1 |3 104 |1 |3 ij> select id,i1,i2 from t2 except all select id,i1,i2 from t1 order by 1,2,3; ID |I1 |I2 ----------------------------------- 100 |1 |3 101 |1 |2 102 |5 |5 103 |1 |3 104 |1 |3 ij> select i1,i2 from t1 except select i1,i2 from t2 order by 1,2; I1 |I2 ----------------------- ij> select i1,i2 from t1 except distinct select i1,i2 from t2 order by 1,2; I1 |I2 ----------------------- ij> select i1,i2 from t1 except all select i1,i2 from t2 order by 1,2; I1 |I2 ----------------------- NULL |NULL ij> select i1,i2 from t2 except select i1,i2 from t1 order by 1,2; I1 |I2 ----------------------- 5 |5 ij> select i1,i2 from t2 except all select i1,i2 from t1 order by 1,2; I1 |I2 ----------------------- 1 |2 1 |3 5 |5 ij> -- right side is empty select i1,i2 from t1 except select i1,i2 from t2 where id = -1 order by 1,2; I1 |I2 ----------------------- 1 |1 1 |2 1 |3 NULL |NULL ij> select i1,i2 from t1 except all select i1,i2 from t2 where id = -1 order by 1,2; I1 |I2 ----------------------- 1 |1 1 |2 1 |3 1 |3 NULL |NULL NULL |NULL ij> -- left side is empty select i1,i2 from t1 where id = -1 except select i1,i2 from t2 order by 1,2; I1 |I2 ----------------------- ij> select i1,i2 from t1 where id = -1 except all select i1,i2 from t2 order by 1,2; I1 |I2 ----------------------- ij> -- Check precedence. Union and except have the same precedence. Intersect has higher precedence. select i1,i2 from t1 except select i1,i2 from t2 intersect values(-1,-1) order by 1,2; 1 |2 ----------------------- 1 |1 1 |2 1 |3 NULL |NULL ij> select i1,i2 from t1 except (select i1,i2 from t2 intersect values(-1,-1)) order by 1,2; 1 |2 ----------------------- 1 |1 1 |2 1 |3 NULL |NULL ij> select i1,i2 from t2 except select i1,i2 from t1 union values(5,5) order by 1,2; 1 |2 ----------------------- 5 |5 ij> (select i1,i2 from t2 except select i1,i2 from t1) union values(5,5) order by 1,2; 1 |2 ----------------------- 5 |5 ij> select i1,i2 from t2 except all select i1,i2 from t1 except select i1,i2 from t1 where id = 3 order by 1,2; I1 |I2 ----------------------- 1 |2 5 |5 ij> (select i1,i2 from t2 except all select i1,i2 from t1) except select i1,i2 from t1 where id = 3 order by 1,2; I1 |I2 ----------------------- 1 |2 5 |5 ij> -- check conversions select c10 from t1 except select vc20 from t2 order by 1; 1 -------------------- b NULL ij> select c30 from t1 except select vc20 from t2 order by 1; 1 ------------------------------ 1.0 123456789012345678901234567890 5 ij> select c30 from t1 except all select vc20 from t2; 1 ------------------------------ 1.0 123456789012345678901234567890 5 bb ij> -- check insert except into table and except without order by insert into r select i1,i2 from t2 except select i1,i2 from t1; 1 row inserted/updated/deleted ij> select i1,i2 from r order by 1,2; I1 |I2 ----------------------- 5 |5 ij> delete from r; 1 row inserted/updated/deleted ij> insert into r select i1,i2 from t2 except all select i1,i2 from t1; 3 rows inserted/updated/deleted ij> select i1,i2 from r order by 1,2; I1 |I2 ----------------------- 1 |2 1 |3 5 |5 ij> delete from r; 3 rows inserted/updated/deleted ij> -- test LOB select cl from t3 except select cl from t4 order by 1; ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> select bl from t3 except select bl from t4 order by 1; ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- invalid conversion select tm from t1 except select dt from t2; ERROR 42X61: Types 'TIME' and 'DATE' are not EXCEPT compatible. ij> select c30 from t1 except select d from t2; ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not EXCEPT compatible. ij> -- different number of columns select i1 from t1 except select i1,i2 from t2; ERROR 42X58: The number of columns on the left and right sides of the EXCEPT must be the same. ij> -- ? in select list of except select ? from t1 except select i1 from t2; ERROR 42X34: There is a ? parameter in the select list. This is not allowed. ij> -- Invalid order by select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by t1.i1; ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY clause. ij> select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1; ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY clause. ij> -- views using intersect and except create view view_intr_uniq as select id,i1,i2 from t1 intersect select id,i1,i2 from t2; 0 rows inserted/updated/deleted ij> select * from view_intr_uniq order by 1 DESC,2,3; ID |I1 |I2 ----------------------------------- 5 |NULL |NULL 2 |1 |2 1 |1 |1 ij> create view view_intr_all as select id,i1,i2 from t1 intersect all select id,i1,i2 from t2; 0 rows inserted/updated/deleted ij> select * from view_intr_all order by 1,2,3; ID |I1 |I2 ----------------------------------- 1 |1 |1 2 |1 |2 5 |NULL |NULL ij> create view view_ex_uniq as select id,i1,i2 from t1 except select id,i1,i2 from t2; 0 rows inserted/updated/deleted ij> select * from view_ex_uniq order by 1,2,3; ID |I1 |I2 ----------------------------------- 3 |1 |3 4 |1 |3 6 |NULL |NULL ij> create view view_ex_all as select id,i1,i2 from t1 except all select id,i1,i2 from t2; 0 rows inserted/updated/deleted ij> select * from view_ex_all order by 1 DESC,2,3; ID |I1 |I2 ----------------------------------- 6 |NULL |NULL 4 |1 |3 3 |1 |3 ij> -- intersect joins select t1.id,t1.i1,t2.i1 from t1 join t2 on t1.id = t2.id intersect select t1.id,t1.i2,t2.i2 from t1 join t2 on t1.id = t2.id; ID |2 |3 ----------------------------------- 1 |1 |1 5 |NULL |NULL ij> -- DERBY-4433: Insert from INTERSECT/EXCEPT into subset of columns create table d4433_t1(x int); 0 rows inserted/updated/deleted ij> insert into d4433_t1 values 1,2,3,4; 4 rows inserted/updated/deleted ij> create table d4433_t2(x int); 0 rows inserted/updated/deleted ij> insert into d4433_t2 values 3,4,5,6; 4 rows inserted/updated/deleted ij> create table d4433_t3(x int, y int); 0 rows inserted/updated/deleted ij> insert into d4433_t3(x) select x from d4433_t1 intersect select x from d4433_t2; 2 rows inserted/updated/deleted ij> select * from d4433_t3 order by x, y; X |Y ----------------------- 3 |NULL 4 |NULL ij> insert into d4433_t3(x) select x from d4433_t1 except select x from d4433_t2; 2 rows inserted/updated/deleted ij> select * from d4433_t3 order by x, y; X |Y ----------------------- 1 |NULL 2 |NULL 3 |NULL 4 |NULL ij>