outerjoin.sql

derby database source code.good for you.

-- test outer joins
-- (NO NATURAL JOIN)


autocommit off;

-- create some tables

create table t1(c1 int);
create table t2(c1 int);
create table t3(c1 int);
create table tt1(c1 int, c2 int, c3 int);
create table tt2(c1 int, c2 int, c3 int);
create table tt3(c1 int, c2 int, c3 int);
create table empty_table(c1 int);
create table insert_test(c1 int, c2 int, c3 int);
-- following is verifying that oj is not a keyword
create table oj(oj int);

-- populate the tables
insert into t1 values 1, 2, 2, 3, 4;
insert into t2 values 1, 3, 3, 5, 6;
insert into t3 values 2, 3, 5, 5, 7;
insert into tt1 select c1, c1, c1 from t1;
insert into tt2 select c1, c1, c1 from t2;
insert into tt3 select c1, c1, c1 from t3;
-- verifying that oj is not a keyword
insert into oj(oj) values (1);

-- negative tests

-- no outer join type
select * from t1 outer join t2;

-- no join clause
select * from t1 left outer join t2;
select * from t1 right outer join t2;


-- positive tests

select t1.c1 from t1 left outer join t2 on t1.c1 = t2.c1;
select t2.c1 from t1 right outer join t2 on t1.c1 = t2.c1;
select a.x from t1 a (x) left outer join t2 b (x) on a.x = b.x;

-- verify that selects from inner table work
select b.* from (values 9) a left outer join t2 b on 1=1;
select b.* from (values 9) a left outer join t2 b on 1=0;
select b.* from (values 9) a right outer join t2 b on 1=0;
select a.* from (values 9) a right outer join t2 b on 1=1;
select a.* from (values 9) a right outer join t2 b on 1=0;
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;
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;
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;

-- verifying that oj is not a keyword
select * from oj where oj = 1;

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

-- 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;
select t1.c1 from t1 right outer join empty_table et on t1.c1 = et.c1;
select t1.c1 from empty_table et right outer join t1 on et.c1 = t1.c1;

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

-- parameters and join clause
prepare asdf as 'select * from t1 left outer join t2 on 1=? and t1.c1 = t2.c1';
execute asdf using 'values 1';
remove asdf;

prepare asdf as 'select * from t1 left outer join t2 on t1.c1 = t2.c1 and t1.c1 = ?';
execute asdf using 'values 1';
remove asdf;

-- 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;
select * from {oj t1 left outer join t2 on t1.c1 = t2.c1} where t1.c1 = 1;
select * from t1 right outer join t2 on t1.c1 = 1 where t2.c1 = t1.c1;
select * from {oj t1 right outer join t2 on t1.c1 = 1} where t2.c1 = t1.c1;

-- 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);
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)};
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);
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)};
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);

-- 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);
select * from t1 a
where exists (select * from {oj t1 left outer join t2 on t1.c1 = t2.c1});
select * from t1 a
where exists (select * from t1 left outer join t2 on 1=0);

-- 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;
select * from {oj t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t1.c1 = 
t3.c1};
select * from t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t2.c1 = t3.c1;
select * from t3 right outer join t2 on t3.c1 = t2.c1 right outer join t1 on t1.c1 = t2.c1;

-- parens
select * from (t1 left outer join t2 on t1.c1 = t2.c1) left outer join t3 on t1.c1 = t3.c1;
select * from t1 left outer join (t2 left outer join t3 on t2.c1 = t3.c1) on t1.c1 = t2.c1;

-- left/right outer join combinations
select * from t1 a right outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1;
select * from (t1 a right outer join t2 b on a.c1 = b.c1) left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1;

select * from t1 a left outer join t2 b on a.c1 = b.c1 right outer join t3 c on c.c1 = a.c1 where a.c1 is not null;
select * from (t1 a left outer join t2 b on a.c1 = b.c1) right outer join t3 c on c.c1 = a.c1 where a.c1 is not null;
select * from t1 a left outer join (t2 b right outer join t3 c on c.c1 = b.c1) on a.c1 = c.c1 where c.c1=b.c1;

-- test insert/update/delete
insert into insert_test
select * from t1 a left outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <> c.c1;
select * from insert_test;

update insert_test
set c1 = (select 9 from t1 a left outer join t1 b on a.c1 = b.c1 where a.c1 = 1)
where c1 = 1;
select * from insert_test;

delete from insert_test
where c1 = (select 9 from t1 a left outer join t1 b on a.c1 = b.c1 where a.c1 = 1);
select * from insert_test;

delete from insert_test;

insert into insert_test
select * from (select * from t1 a left outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <> c.c1) d (c1, c2, c3);
select * from insert_test;
delete from insert_test;

-- verify that right outer join xforms don't get result columns
-- confused
create table a (c1 int);
create table b (c2 float);
create table c (c3 char(30));

insert into a values 1;
insert into b values 3.3;
insert into c values 'asdf';

select * from a left outer join b on 1=1 left outer join c on 1=1;
select * from a left outer join b on 1=1 left outer join c on 1=0;
select * from a left outer join b on 1=0 left outer join c on 1=1;
select * from a left outer join b on 1=0 left outer join c on 1=0;

select * from c right outer join b on 1=1 right outer join a on 1=1;
select * from c right outer join b on 1=1 right outer join a on 1=0;
select * from c right outer join b on 1=0 right outer join a on 1=1;
select * from c right outer join b on 1=0 right outer join a on 1=0;

-- multicolumn tests
-- c1, c2, and c3 all have the same values
select tt1.c1, tt1.c2, tt1.c3, tt2.c2, tt2.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;
select tt1.c1, tt1.c2, tt1.c3, tt2.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;
select tt1.c1, tt1.c2, tt1.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;

-- nested outer joins
select tt1.c2, tt1.c1, tt1.c3, tt2.c1, tt2.c3 from t1 left outer join tt1 on t1.c1 = tt1.c1 left outer join tt2 on tt1.c2 = tt2.c2;

-- make sure that column reordering is working correctly 
-- when there's an ON clause
create table x (c1 int, c2 int, c3 int);
create table y (c3 int, c4 int, c5 int);
insert into x values (1, 2, 3), (4, 5, 6);
insert into y values (3, 4, 5), (666, 7, 8);

-- qualfied * will return all of the columns of the qualified table
-- including join columns
select x.* from x join y on x.c3 = y.c3;
select x.* from x left outer join y on x.c3 = y.c3;
select x.* from x right outer join y on x.c3 = y.c3;
select y.* from x join y on x.c3 = y.c3;
select y.* from x left outer join y on x.c3 = y.c3;
select y.* from x right outer join y on x.c3 = y.c3;

-- * will return all of the columns of all joined tables
select * from x join y on x.c3 = y.c3;
select * from x left outer join y on x.c3 = y.c3;
select * from x right outer join y on x.c3 = y.c3;

commit;

-- test outer join -> inner join xform
delete from tt1;
delete from tt2;
delete from tt3;

insert into tt1 values (1, 2, 3), (2, 3, 4), (3, 4, 5);
insert into tt2 values (1, 2, 3), (2, 3, 4), (3, 4, 5);
insert into tt3 values (1, 2, 3), (2, 3, 4), (3, 4, 5);

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 4500;

-- no xform, predicate on outer table
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt1.c1 = 3;

-- various predicates on inner table
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c2 = 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= tt2.c2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt2 right outer join tt1 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt3.c3 = 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt2.c2 = 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- make sure predicates are null tolerant 
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 
where char(tt2.c2) is null;
-- where java.lang.Integer::toString(tt2.c2) = '2';
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
commit;

-- bug 2924, cross join under an outer join
CREATE TABLE inventory(itemno INT NOT NULL PRIMARY KEY, capacity INT);

INSERT INTO inventory VALUES (1, 4);
INSERT INTO inventory VALUES (2, 2);
INSERT INTO inventory VALUES (3, 2);

CREATE TABLE timeslots (slotno INT NOT NULL PRIMARY KEY);

INSERT INTO timeslots VALUES(1);
INSERT INTO timeslots VALUES(2);

create table reservations(slotno INT CONSTRAINT timeslots_fk REFERENCES timeslots, 
itemno INT CONSTRAINT inventory_fk REFERENCES inventory, 
name VARCHAR(100), resdate DATE);

    
INSERT INTO reservations VALUES(1, 1, 'Joe', '2000-04-14');
INSERT INTO reservations VALUES(1, 1, 'Fred', '2000-04-13');

-- This query used to cause a null pointer exception