📄 union.out
字号:
2 |4 NULL |NULL 3 |NULL 4 |NULL NULL |1 3 |1 4 |1 NULL |2 3 |2 4 |2 ij> select a.i, b.i from t1 a, t2 b union all select b.i, a.i from t1 a, t2 b union all values (9, 10);1 |2 -----------------------NULL |NULL NULL |3 NULL |4 1 |NULL 1 |3 1 |4 2 |NULL 2 |3 2 |4 NULL |NULL 3 |NULL 4 |NULL NULL |1 3 |1 4 |1 NULL |2 3 |2 4 |2 9 |10 ij> -- incompatible typesselect date('9999-11-11') from t1 union all select time('11:11:11') from t2;ERROR 42X61: Types 'DATE' and 'TIME' are not UNION compatible.ij> -- non-correlated subqueries-- negative tests-- select * in subqueryselect i from t1 where i = (select * from t2 union all select 1 from t1);ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.ij> select i from t1 where i = (select 1 from t2 union all select * from t1);ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.ij> -- too many columnsselect i from t1 where i = (values (1, 2, 3) union all values (1, 2, 3));ERROR 42X39: Subquery is only allowed to return a single column.ij> select i from t1 where i = (select i, s from t2 union all select i, s from t1);ERROR 42X39: Subquery is only allowed to return a single column.ij> -- cardinality violationselect i from t1 where i = (values 1 union all values 1);ERROR 21000: Scalar subquery is only allowed to return a single row.ij> -- both sides of union have same type, which is incompatible with LHSselect i from t1 where i in (select date('1999-02-04') from t2 union all select date('1999-03-08') from t2);ERROR 42818: Comparisons between 'INTEGER' and 'DATE' are not supported.ij> -- positive tests-- expression subqueryselect i from t1 where i = (select i from t2 where 1 = 0 union all values 1);I -----------1 ij> -- in subqueryselect i from t1 where i in (select i from t2 union all values 1 union all values 2);I -----------1 2 ij> select i from t1 where i in (select a from (select i from t2 union all values 1 union all values 2) a (a));I -----------1 2 ij> -- not in subqueryselect i from t1 where i not in (select i from t2 union all values 1 union all values 2);I -----------ij> select i from t1 where i not in (select i from t2 where i is not null union all values 1 union all values 22);I -----------2 ij> select i from t1 where i not in (select a from (select i from t2 where i is not null union all values 111 union all values 2) a (a));I -----------1 ij> -- correlated union subqueryselect i from t1 a where i in (select i from t2 where 1 = 0 union all select a.i from t2 where a.i < i);I -----------1 2 ij> select i from t1 a where i in (select a.i from t2 where a.i < i union all select i from t2 where 1 < 0);I -----------1 2 ij> -- exists subqueryselect i from t1 where exists (select * from t2 union all select * from t2);I -----------NULL 1 2 ij> select i from t1 where exists (select 1 from t2 union all select 2 from t2);I -----------NULL 1 2 ij> select i from t1 where exists (select 1 from t2 where 1 = 0 union all select * from t2 where t1.i < i);I -----------1 2 ij> select i from t1 where exists (select i from t2 where t1.i < i union all select * from t2 where 1 = 0 union all select * from t2 where t1.i < i union all select i from t2 where 1 = 0);I -----------1 2 ij> -- insert testscreate table insert_test (i int, s smallint, d double precision, r real, c10 char(10), c30 char(30), vc10 varchar(10), vc30 varchar(30));0 rows inserted/updated/deletedij> -- simple testsinsert into insert_test select * from t1 union all select * from t2;6 rows inserted/updated/deletedij> select * from insert_test;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 ij> delete from insert_test;6 rows inserted/updated/deletedij> insert into insert_test (s, i) values (2, 1) union all values (4, 3);2 rows inserted/updated/deletedij> select * from insert_test;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------1 |2 |NULL |NULL |NULL |NULL |NULL |NULL 3 |4 |NULL |NULL |NULL |NULL |NULL |NULL ij> delete from insert_test;2 rows inserted/updated/deletedij> -- type conversions between union all and target tableinsert into insert_test select s, i, r, d, vc10, vc30, c10, c30 from t1 union all select s, i, r, d, vc10, vc30, c10, vc30 from t2;6 rows inserted/updated/deletedij> select * from insert_test;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 ij> delete from insert_test;6 rows inserted/updated/deletedij> -- test type dominance/length/nullabilityselect vc10 from t1 union all select c30 from t2;1 ------------------------------NULL 11111 22222 NULL 33333 33 44444 44 ij> insert into insert_test (vc30) select vc10 from t1 union all select c30 from t2;6 rows inserted/updated/deletedij> select * from insert_test;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |11111 NULL |NULL |NULL |NULL |NULL |NULL |NULL |22222 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |33333 33 NULL |NULL |NULL |NULL |NULL |NULL |NULL |44444 44 ij> delete from insert_test;6 rows inserted/updated/deletedij> insert into insert_test (c30) select vc10 from t1 union all select c30 from t2 union all select c10 from t1;9 rows inserted/updated/deletedij> select * from insert_test;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |11111 |NULL |NULL NULL |NULL |NULL |NULL |NULL |22222 |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |33333 33 |NULL |NULL NULL |NULL |NULL |NULL |NULL |44444 44 |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |11111 |NULL |NULL NULL |NULL |NULL |NULL |NULL |22222 |NULL |NULL ij> delete from insert_test;9 rows inserted/updated/deletedij> -- test NormalizeResultSet generationselect i, d from t1 union all select d, i from t2;1 |2 ---------------------------------------------NULL |NULL 1.0 |10.0 2.0 |20.0 NULL |NULL 30.0 |3.0 40.0 |4.0 ij> select vc10, c30 from t1 union all select c30, vc10 from t2;1 |2 -------------------------------------------------------------NULL |NULL 11111 |11111 11 22222 |22222 22 NULL |NULL 33333 33 |33333 44444 44 |44444 ij> create table insert_test2 (s smallint not null, vc30 varchar(30) not null);0 rows inserted/updated/deletedij> -- the following should fail due to null constraintinsert into insert_test2 select s, c10 from t1 union all select s, c30 from t2;ERROR 23502: Column 'S' cannot accept a NULL value.ij> select * from insert_test2;S |VC30 -------------------------------------ij> -- negative tests-- ? in select list of unionselect ? from insert_test union all select vc30 from insert_test;ERROR 42X34: There is a ? parameter in the select list. This is not allowed.ij> select vc30 from insert_test union all select ? from insert_test;ERROR 42X34: There is a ? parameter in the select list. This is not allowed.ij> -- DB2 requires matching target and result columnsinsert into insert_test values (1, 2) union all values (3, 4);ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.ij> -- Beetle 4454 - test multiple union alls in a subqueryselect vc10 from (select vc10 from t1 union allselect vc10 from t1 union allselect vc10 from t1 union allselect vc10 from t1 union allselect vc10 from t1 union allselect vc10 from t1 union allselect vc10 from t1) t;VC10 ----------NULL 11111 22222 NULL 11111 22222 NULL 11111 22222 NULL 11111 22222 NULL 11111 22222 NULL 11111 22222 NULL 11111 22222 ij> -- force union all on right sideselect vc10 from (select vc10 from t1 union all (select vc10 from t1 union allselect vc10 from t1)) t;VC10 ----------NULL 11111 22222 NULL 11111 22222 NULL 11111 22222 ij> -- drop the tablesdrop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table insert_test;0 rows inserted/updated/deletedij> drop table insert_test2;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -