--
-- ### Outer join checks
--
drop if exists table t1;
drop if exists table t2;
drop if exists table t3;
drop if exists view v1;

create table t1 ( primary a int not null, b string(30));
create table t2 ( primary c int not null, d string(30));
create table t3 ( primary e int not null, f string(30));

insert into t1 values ( 1, 'xxx');
insert into t1 values ( 2, 'RRR');
insert into t1 values ( 4, 'yyy');
-- insert into t1 values ( 3, 'zzz');

insert into t2 values ( 1, 'xxx');
insert into t2 values ( 2, 'vvv');
insert into t2 values ( 3, 'zzz');

insert into t3 values ( 1, 'xxx');
insert into t3 values ( 2, 'vvv');
insert into t3 values ( 3, 'zzz');

select * from t1 tx inner join t2 ty on tx.a = ty.c inner join t3 tz on ( tz.e = ty.c or tz.e = 2 ); 
-- in (select a from t1 where b = 'xxx'));

select * from t1 inner join t2 on t1.a = t2.c where t2.d = 'xxx';
select * from t1 inner join t2 on t1.a = t2.c inner join t3 on t3.e = t2.c where t3.f = 'zzz';
select * from t1 right outer join t2 on t1.a = t2.c inner join t3 on t3.e = t2.c;
select * from t1 inner join t2 on t1.a = t2.c right outer join t3 on t3.e = t2.c;

create view v1 as select t1.a as A, t2.d as B from t1 inner join t2 on t1.a = t2.c;

select * from t1, t2 where t1.a = t2.c;
select * from t1 inner join t2 on t1.a = t2.c;
select * from t1 left outer join t2 on t1.a = t2.c;
select * from t1 tx right outer join t2 ty on tx.a = ty.c;
select A from v1; 
select A from v1 where B = 'xxx'; 

drop if exists view v1;
drop if exists table t1;
drop if exists table t2;
drop if exists table t3;


