HSQLDB example source code file (TestSelfUnions.txt)

This example HSQLDB source code file (TestSelfUnions.txt) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

a, b, c, create, from, from, insert, insert, into, into, select, table, values, values

The HSQLDB TestSelfUnions.txt source code

drop 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;