ij> 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/deletedij> create table t2( id integer not null primary key, i1 integer, i2 integer, vc20 varchar(20), d double, dt date);0 rows inserted/updated/deletedij> 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/deletedij> 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/deletedij> -- no duplicatesselect 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 columnsselect 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> -- duplicatesselect 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 emptyselect 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 emptyselect i1,i2 from t1 where id = -1 intersect all select i1,i2 from t2;I1         |I2         -----------------------ij> -- check precedenceselect 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 conversionsselect 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 bycreate table r( i1 integer, i2 integer);0 rows inserted/updated/deletedij> insert into r select i1,i2 from t1 intersect select i1,i2 from t2;4 rows inserted/updated/deletedij> 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/deletedij> insert into r select i1,i2 from t1 intersect all select i1,i2 from t2;5 rows inserted/updated/deletedij> 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/deletedij> -- test LOBcreate table t3( i1 integer, cl clob(64), bl blob(1M));0 rows inserted/updated/deletedij> insert into t3 values  (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));1 row inserted/updated/deletedij> create table t4( i1 integer, cl clob(64), bl blob(1M));0 rows inserted/updated/deletedij> insert into t4 values  (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));1 row inserted/updated/deletedij> 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 conversionselect 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 columnsselect 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 intersectselect ? 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 testsselect 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         -----------------------------------