|
HSQLDB example source code file (TestSelfUnions.txt)
The HSQLDB TestSelfUnions.txt source codedrop table a if exists; drop table b if exists; drop table c if exists; CREATE TABLE A(ID INTEGER NOT NULL) CREATE TABLE B(ID INTEGER NOT NULL) CREATE TABLE C(ID INTEGER NOT NULL) INSERT INTO A VALUES(1) INSERT INTO A VALUES(2) INSERT INTO A VALUES(3) INSERT INTO B VALUES(1) INSERT INTO B VALUES(2) INSERT INTO C VALUES(3) INSERT INTO C VALUES(4) INSERT INTO C VALUES(5) /*e*/SELECT * FROM A UNION (SELECT * FROM C /*e*/SELECT * FROM A UNION SELECT * FROM C) /*e*/(SELECT * FROM A UNION (SELECT * FROM C /*e*/(SELECT * FROM A /*c5*/SELECT * FROM A UNION SELECT * FROM C /*c5*/(SELECT * FROM A) UNION SELECT * FROM C /*c5*/SELECT * FROM A UNION (SELECT * FROM C) /*c5*/(SELECT * FROM A UNION SELECT * FROM C) -- limits /*c5*/SELECT * FROM A UNION SELECT * FROM C ORDER BY ID /*c4*/(SELECT * FROM A ORDER BY ID LIMIT 1 OFFSET 0) UNION SELECT * FROM C /*c4*/SELECT * FROM A UNION (SELECT * FROM C ORDER BY ID LIMIT 2 OFFSET 0) LIMIT 4 OFFSET 0 /*r 2 3 4 */SELECT * FROM A UNION (SELECT * FROM C ORDER BY ID LIMIT 2 OFFSET 0) LIMIT 0 OFFSET 1 /*r 4 3 2 1 */SELECT * FROM A UNION (SELECT * FROM C ORDER BY ID LIMIT 2 OFFSET 0) ORDER BY ID DESC --LIMIT 0 OFFSET 1 /*r 4 5 */SELECT * FROM A UNION SELECT * FROM C ORDER BY ID LIMIT 2 OFFSET 3 -- /*c3*/SELECT * FROM A UNION SELECT * FROM B /*c5*/SELECT * FROM B UNION SELECT * FROM C /*c2*/SELECT * FROM A EXCEPT SELECT * FROM C /*c2*/(SELECT * FROM A) EXCEPT SELECT * FROM C /*c2*/SELECT * FROM A EXCEPT (SELECT * FROM C) /*c1*/(SELECT * FROM A INTERSECT SELECT * FROM C) /*c2*/SELECT * FROM A INTERSECT (SELECT * FROM B) /*c0*/(SELECT * FROM B) INTERSECT SELECT * FROM C /*r 1 2 3 */SELECT * FROM A UNION SELECT * FROM C INTERSECT SELECT * FROM B /*r 1 2 */(SELECT * FROM A UNION SELECT * FROM C) INTERSECT SELECT * FROM B /*r */SELECT * FROM A EXCEPT SELECT * FROM C EXCEPT SELECT * FROM B /*r 3 */SELECT * FROM A UNION SELECT * FROM C INTERSECT SELECT * FROM B EXCEPT SELECT * FROM B /*r 1 2 3 */SELECT * FROM A UNION SELECT * FROM C INTERSECT (SELECT * FROM B EXCEPT SELECT * FROM B) CREATE VIEW V1 AS (SELECT * FROM A UNION SELECT * FROM C) INTERSECT SELECT * FROM B /*r 1 2 */SELECT * FROM V1 CREATE VIEW V3 AS SELECT * FROM A UNION SELECT * FROM C INTERSECT (SELECT * FROM B EXCEPT SELECT * FROM B) /*r 1 2 3 */SELECT * FROM V3; /*c3*/SELECT * FROM A WHERE ID IN (SELECT * FROM A UNION SELECT * FROM C INTERSECT (SELECT * FROM B EXCEPT SELECT * FROM B)) /*c3*/SELECT * FROM A WHERE EXISTS (SELECT * FROM A UNION SELECT * FROM C INTERSECT (SELECT * FROM B EXCEPT SELECT * FROM B)) /*c0*/SELECT * FROM A WHERE EXISTS (SELECT * FROM A EXCEPT SELECT * FROM C EXCEPT SELECT * FROM B) --- correlated union create table bt1 ( id varchar(10) ); create table bt2 ( id varchar(10) ); create table bt3 ( id varchar(10) ); insert into bt1( id ) values ( 'aaa' ); insert into bt1( id ) values ( 'bbb' ); insert into bt1( id ) values ( 'ccc' ); insert into bt2( id ) values ( 'aaa' ); insert into bt3( id ) values ( 'bbb' ); /*c1*/select * from bt1 as t1 where not exists( select id from bt2 as t2 where t2.id = t1.id union all select id from bt3 as t3 where t3.id = t1.id ); --- null as column in first select create table au (id int, b1 bit); create table bu (id int); insert into au values(1,true); insert into bu values(2); select id, null as b1 from bu union select id, b1 from au; Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfUnions.txt source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.