📄 intersect.out
字号:
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 emptyselect 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 emptyselect 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 conversionsselect 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 1234567890123456789012345678905 ij> select c30 from t1 except all select vc20 from t2;1 ------------------------------1.0 1234567890123456789012345678905 bb ij> -- check insert except into table and except without order byinsert into r select i1,i2 from t2 except select i1,i2 from t1;1 row inserted/updated/deletedij> select i1,i2 from r order by 1,2;I1 |I2 -----------------------5 |5 ij> delete from r;1 row inserted/updated/deletedij> insert into r select i1,i2 from t2 except all select i1,i2 from t1;3 rows inserted/updated/deletedij> 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/deletedij> -- test LOBselect 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 conversionselect 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 columnsselect 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 exceptselect ? from t1 except select i1 from t2;ERROR 42X34: There is a ? parameter in the select list. This is not allowed.ij> -- Invalid order byselect 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 exceptcreate view view_intr_uniq as select id,i1,i2 from t1 intersect select id,i1,i2 from t2;0 rows inserted/updated/deletedij> 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/deletedij> 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/deletedij> 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/deletedij> 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 joinsselect t1.id,t1.i1,t2.i1 from t1 join t2 on t1.id = t2.idintersect 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> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -