📄 union.out
字号:
ij> ---- this test shows union functionality---- create the tablescreate table t1 (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> create table t2 (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> create table dups (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> -- populate the tablesinsert into t1 values (null, null, null, null, null, null, null, null);1 row inserted/updated/deletedij> insert into t1 values (1, 1, 1e1, 1e1, '11111', '11111 11', '11111', '11111 11');1 row inserted/updated/deletedij> insert into t1 values (2, 2, 2e1, 2e1, '22222', '22222 22', '22222', '22222 22');1 row inserted/updated/deletedij> insert into t2 values (null, null, null, null, null, null, null, null);1 row inserted/updated/deletedij> insert into t2 values (3, 3, 3e1, 3e1, '33333', '33333 33', '33333', '33333 33');1 row inserted/updated/deletedij> insert into t2 values (4, 4, 4e1, 4e1, '44444', '44444 44', '44444', '44444 44');1 row inserted/updated/deletedij> insert into dups select * from t1 union all select * from t2;6 rows inserted/updated/deletedij> -- simple casesvalues (1, 2, 3, 4) union values (5, 6, 7, 8);1 |2 |3 |4 -----------------------------------------------1 |2 |3 |4 5 |6 |7 |8 ij> values (1, 2, 3, 4) union values (1, 2, 3, 4);1 |2 |3 |4 -----------------------------------------------1 |2 |3 |4 ij> values (1, 2, 3, 4) union distinct values (5, 6, 7, 8);1 |2 |3 |4 -----------------------------------------------1 |2 |3 |4 5 |6 |7 |8 ij> values (1, 2, 3, 4) union distinct values (1, 2, 3, 4);1 |2 |3 |4 -----------------------------------------------1 |2 |3 |4 ij> values (1, 2, 3, 4) union values (5, 6, 7, 8) union values (9, 10, 11, 12);1 |2 |3 |4 -----------------------------------------------1 |2 |3 |4 5 |6 |7 |8 9 |10 |11 |12 ij> values (1, 2, 3, 4) union values (1, 2, 3, 4) union values (1, 2, 3, 4);1 |2 |3 |4 -----------------------------------------------1 |2 |3 |4 ij> select * from t1 union select * from t2;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from t1 union select * from t1;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------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 ij> select * from t1 union select * from t2 union select * from dups;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from t1 union select i, s, d, r, c10, c30, vc10, vc30 from t2;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from t1 union select i, s, d, r, c10, c30, vc10, vc30 from t2 union select * from dups;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> -- derived tablesselect * from (values (1, 2, 3, 4) union values (5, 6, 7, 8)) a;1 |2 |3 |4 -----------------------------------------------1 |2 |3 |4 5 |6 |7 |8 ij> select * from (values (1, 2, 3, 4) union values (5, 6, 7, 8) union values (1, 2, 3, 4)) a;1 |2 |3 |4 -----------------------------------------------1 |2 |3 |4 5 |6 |7 |8 ij> -- mix unions and union allsselect i from t1 union select i from t2 union all select i from dups;I -----------1 2 3 4 NULL NULL 1 2 NULL 3 4 ij> (select i from t1 union select i from t2) union all select i from dups;I -----------1 2 3 4 NULL NULL 1 2 NULL 3 4 ij> select i from t1 union (select i from t2 union all select i from dups);I -----------1 2 3 4 NULL ij> select i from t1 union all select i from t2 union select i from dups;I -----------1 2 3 4 NULL ij> (select i from t1 union all select i from t2) union select i from dups;I -----------1 2 3 4 NULL ij> select i from t1 union all (select i from t2 union select i from dups);I -----------NULL 1 2 1 2 3 4 NULL ij> -- joinsselect a.i, b.i from t1 a, t2 b union select b.i, a.i from t1 a, t2 b;I |I -----------------------1 |3 1 |4 1 |NULL 2 |3 2 |4 2 |NULL 3 |1 3 |2 3 |NULL 4 |1 4 |2 4 |NULL NULL |1 NULL |2 NULL |3 NULL |4 NULL |NULL ij> values (9, 10) union select a.i, b.i from t1 a, t2 b union select b.i, a.i from t1 a, t2 b;1 |2 -----------------------1 |3 1 |4 1 |NULL 2 |3 2 |4 2 |NULL 3 |1 3 |2 3 |NULL 4 |1 4 |2 4 |NULL 9 |10 NULL |1 NULL |2 NULL |3 NULL |4 NULL |NULL ij> select a.i, b.i from t1 a, t2 b union select b.i, a.i from t1 a, t2 b union values (9, 10);1 |2 -----------------------1 |3 1 |4 1 |NULL 2 |3 2 |4 2 |NULL 3 |1 3 |2 3 |NULL 4 |1 4 |2 4 |NULL 9 |10 NULL |1 NULL |2 NULL |3 NULL |4 NULL |NULL ij> -- non-correlated subqueries-- positive testsselect i from t1 where i = (values 1 union values 1);I -----------1 ij> select i from t1 where i = (values 1 union values 1 union values 1);I -----------1 ij> -- expression subqueryselect i from t1 where i = (select 1 from t2 union values 1);I -----------1 ij> -- in subqueryselect i from t1 where i in (select i from t2 union values 1 union values 2);I -----------1 2 ij> select i from t1 where i in (select a from (select i from t2 union values 1 union values 2) a (a));I -----------1 2 ij> -- not in subqueryselect i from t1 where i not in (select i from t2 union values 1 union values 2);I -----------ij> select i from t1 where i not in (select i from t2 where i is not null union values 1 union 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 values 111 union 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 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 select i from t2 where 1 < 0);I -----------1 2 ij> -- exists subqueryselect i from t1 where exists (select * from t2 union select * from t2);I -----------NULL 1 2 ij> select i from t1 where exists (select 1 from t2 union select 2 from t2);I ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -