outerjoin.out

derby database source code.good for you.

ij> -- test outer joins
-- (NO NATURAL JOIN)
autocommit off;
ij> -- create some tables
create table t1(c1 int);
0 rows inserted/updated/deleted
ij> create table t2(c1 int);
0 rows inserted/updated/deleted
ij> create table t3(c1 int);
0 rows inserted/updated/deleted
ij> create table tt1(c1 int, c2 int, c3 int);
0 rows inserted/updated/deleted
ij> create table tt2(c1 int, c2 int, c3 int);
0 rows inserted/updated/deleted
ij> create table tt3(c1 int, c2 int, c3 int);
0 rows inserted/updated/deleted
ij> create table empty_table(c1 int);
0 rows inserted/updated/deleted
ij> create table insert_test(c1 int, c2 int, c3 int);
0 rows inserted/updated/deleted
ij> -- following is verifying that oj is not a keyword
create table oj(oj int);
0 rows inserted/updated/deleted
ij> -- populate the tables
insert into t1 values 1, 2, 2, 3, 4;
5 rows inserted/updated/deleted
ij> insert into t2 values 1, 3, 3, 5, 6;
5 rows inserted/updated/deleted
ij> insert into t3 values 2, 3, 5, 5, 7;
5 rows inserted/updated/deleted
ij> insert into tt1 select c1, c1, c1 from t1;
5 rows inserted/updated/deleted
ij> insert into tt2 select c1, c1, c1 from t2;
5 rows inserted/updated/deleted
ij> insert into tt3 select c1, c1, c1 from t3;
5 rows inserted/updated/deleted
ij> -- verifying that oj is not a keyword
insert into oj(oj) values (1);
1 row inserted/updated/deleted
ij> -- negative tests
-- no outer join type
select * from t1 outer join t2;
ERROR 42X01: Syntax error: Encountered "outer" at line 4, column 18.
ij> -- no join clause
select * from t1 left outer join t2;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 2, column 35.
ij> select * from t1 right outer join t2;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 36.
ij> -- positive tests
select t1.c1 from t1 left outer join t2 on t1.c1 = t2.c1;
C1         
-----------
1          
2          
2          
3          
3          
4          
ij> select t2.c1 from t1 right outer join t2 on t1.c1 = t2.c1;
C1         
-----------
1          
3          
3          
5          
6          
ij> select a.x from t1 a (x) left outer join t2 b (x) on a.x = b.x;
X          
-----------
1          
2          
2          
3          
3          
4          
ij> -- verify that selects from inner table work
select b.* from (values 9) a left outer join t2 b on 1=1;
C1         
-----------
1          
3          
3          
5          
6          
ij> select b.* from (values 9) a left outer join t2 b on 1=0;
C1         
-----------
NULL       
ij> select b.* from (values 9) a right outer join t2 b on 1=0;
C1         
-----------
1          
3          
3          
5          
6          
ij> select a.* from (values 9) a right outer join t2 b on 1=1;
1          
-----------
9          
9          
9          
9          
9          
ij> select a.* from (values 9) a right outer join t2 b on 1=0;
1          
-----------
NULL       
NULL       
NULL       
NULL       
NULL       
ij> select a.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1;
1   |2   
---------
a   |b   
ij> select b.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1;
1   |2   
---------
c   |d   
ij> select c.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1;
1   |2   
---------
e   |f   
ij> -- verifying that oj is not a keyword
select * from oj where oj = 1;
OJ         
-----------
1          
ij> --verifying both regular and {oj } in 
select * from t1 left outer join {oj t2 left outer join t3 on t2.c1=t3.c1} on t1.c1=t3.c1;
C1         |C1         |C1         
-----------------------------------
1          |NULL       |NULL       
2          |NULL       |NULL       
2          |NULL       |NULL       
3          |3          |3          
3          |3          |3          
4          |NULL       |NULL       
ij> -- left and right outer join with an empty table
select t1.c1 from t1 left outer join empty_table et on t1.c1 = et.c1;
C1         
-----------
1          
2          
2          
3          
4          
ij> select t1.c1 from t1 right outer join empty_table et on t1.c1 = et.c1;
C1         
-----------
ij> select t1.c1 from empty_table et right outer join t1 on et.c1 = t1.c1;
C1         
-----------
1          
2          
2          
3          
4          
ij> -- this query may make no sense at all, but it's just trying to show that parser works
-- fine with both regular tableexpression and tableexpression with {oj }
select * from t1, {oj t2 join t3 on t2.c1=t3.c1};
C1         |C1         |C1         
-----------------------------------
1          |3          |3          
1          |3          |3          
1          |5          |5          
1          |5          |5          
2          |3          |3          
2          |3          |3          
2          |5          |5          
2          |5          |5          
2          |3          |3          
2          |3          |3          
2          |5          |5          
2          |5          |5          
3          |3          |3          
3          |3          |3          
3          |5          |5          
3          |5          |5          
4          |3          |3          
4          |3          |3          
4          |5          |5          
4          |5          |5          
ij> -- parameters and join clause
prepare asdf as 'select * from t1 left outer join t2 on 1=? and t1.c1 = t2.c1';
ij> execute asdf using 'values 1';
C1         |C1         
-----------------------
1          |1          
2          |NULL       
2          |NULL       
3          |3          
3          |3          
4          |NULL       
ij> remove asdf;
ij> prepare asdf as 'select * from t1 left outer join t2 on t1.c1 = t2.c1 and t1.c1 = ?';
ij> execute asdf using 'values 1';
C1         |C1         
-----------------------
1          |1          
2          |NULL       
2          |NULL       
3          |NULL       
4          |NULL       
ij> remove asdf;
ij> -- additional predicates outside of the join clause
-- egs of using {oj --} syntax
select * from t1 left outer join t2 on t1.c1 = t2.c1 where t1.c1 = 1;
C1         |C1         
-----------------------
1          |1          
ij> select * from {oj t1 left outer join t2 on t1.c1 = t2.c1} where t1.c1 = 1;
C1         |C1         
-----------------------
1          |1          
ij> select * from t1 right outer join t2 on t1.c1 = 1 where t2.c1 = t1.c1;
C1         |C1         
-----------------------
1          |1          
ij> select * from {oj t1 right outer join t2 on t1.c1 = 1} where t2.c1 = t1.c1;
C1         |C1         
-----------------------
1          |1          
ij> -- subquery in join clause. Not allowed in the DB2 compatibility mode. ERROR.
-- egs of using {oj --} syntax
select * from t1 a left outer join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1);
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> select * from {oj t1 a left outer join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1)};
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> select * from t1 a left outer join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 <> 2);
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> select * from {oj t1 a left outer join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 <> 2)};
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> select * from t1 a right outer join t2 b 
on a.c1 = b.c1 and a.c1 in (select c1 from t1 where a.c1 = t1.c1);
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> -- outer join in subquery
-- egs of using {oj --} syntax
select * from t1 a
where exists (select * from t1 left outer join t2 on t1.c1 = t2.c1);
C1         
-----------
1          
2          
2          
3          
4          
ij> select * from t1 a
where exists (select * from {oj t1 left outer join t2 on t1.c1 = t2.c1});
C1         
-----------
1          
2          
2          
3          
4          
ij> select * from t1 a
where exists (select * from t1 left outer join t2 on 1=0);
C1         
-----------
1          
2          
2          
3          
4          
ij> -- nested joins
-- egs of using {oj --} syntax
select * from t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t1.c1 = t3.c1;
C1         |C1         |C1         
-----------------------------------
1          |1          |NULL       
2          |NULL       |2          
2          |NULL       |2          
3          |3          |3          
3          |3          |3          
4          |NULL       |NULL       
ij> select * from {oj t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t1.c1 = 
t3.c1};
C1         |C1         |C1         
-----------------------------------
1          |1          |NULL       
2          |NULL       |2          
2          |NULL       |2          
3          |3          |3          
3          |3          |3          
4          |NULL       |NULL       
ij> select * from t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t2.c1 = t3.c1;
C1         |C1         |C1         
-----------------------------------
1          |1          |NULL