innerjoin.out

derby database source code.good for you.

ij> -- test inner 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 insert_test(c1 int, c2 int, c3 int);
0 rows inserted/updated/deleted
ij> -- populate the tables
insert into t1 values 1, 2, 3, 4;
4 rows inserted/updated/deleted
ij> insert into t2 values 1, 3, 5, 6;
4 rows inserted/updated/deleted
ij> insert into t3 values 2, 3, 5, 7;
4 rows inserted/updated/deleted
ij> -- negative tests
-- no join clause
select * from t1 join t2;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 4, column 24.
ij> select * from t1 inner join t2;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 30.
ij> -- empty column list
select * from t1 join t2 using ();
ERROR 42X01: Syntax error: Encountered "using" at line 2, column 26.
ij> -- non-boolean join clause
select * from t1 join t2 on 1;
ERROR 42Y12: The ON clause of a JOIN is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> -- duplicate exposed names, DB2 extension
-- DB2 UDB: PASS
-- DB2 CS:  FAIL
select * from t1 join t1 on 1=1;
ERROR 42X03: Column name 'T1.C1' is in more than one table in the FROM list.
ij> -- duplicate exposed names
select * from t1 join t1 on c1 = 1;
ERROR 42X03: Column name 'T1.C1' is in more than one table in the FROM list.
ij> select * from t1 join t1 on (c1);
ERROR 42X03: Column name 'T1.C1' is in more than one table in the FROM list.
ij> -- join clause only allowed to contain column references from tables being
-- joined. DB2 doesn't allow references to correlated columns
select * from t1, t2 join t3 on t1.c1 = t2.c1;
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> -- should match db2's behavior by raising an error
select * from t2 b inner join t3 c on a.c1 = b.c1 and b.c1 = c.c1;
ERROR 42X04: Column 'A.C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'A.C1' is not a column in the target table.
ij> select * from t3 b where exists (select * from t1 a inner join t2 on b.c1 = t2.c1);
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> select * from t3 where exists (select * from t1 inner join t2 on t3.c1 = t2.c1);
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> -- positive tests
select a.c1 from t1 a join t2 b on a.c1 = b.c1;
C1         
-----------
1          
3          
ij> select a.x from t1 a (x) join t2 b (x) on a.x = b.x;
X          
-----------
1          
3          
ij> -- ANSI "extension" - duplicate exposed names allowed when no column references
-- this may go away if we can figure out how to detect this error and
-- get bored enough to prioritize the fix
get cursor c as 'select 1 from t1 join t1 on 1=1';
ij> next c;
1          
-----------
1          
ij> close c;
ij> -- parameters and join clause
prepare asdf as 'select * from t1 join t2 on ?=1 and t1.c1 = t2.c1';
ij> execute asdf using 'values 1';
C1         |C1         
-----------------------
1          |1          
3          |3          
ij> remove asdf;
ij> prepare asdf as 'select * from t1 join t2 on t1.c1 = t2.c1 and t1.c1 = ?';
ij> execute asdf using 'values 1';
C1         |C1         
-----------------------
1          |1          
ij> remove asdf;
ij> -- additional predicates outside of the join clause
select * from t1 join t2 on t1.c1 = t2.c1 where t1.c1 = 1;
C1         |C1         
-----------------------
1          |1          
ij> select * from t1 join t2 on t1.c1 = 1 where t2.c1 = t1.c1;
C1         |C1         
-----------------------
1          |1          
ij> -- subquery in join clause, not allowed in DB2 compatibility mode
select * from t1 a join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1);
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> select * from t1 a 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> -- correlated columns
select * from t1 a
where exists (select * from t1 inner join t2 on a.c1 = t2.c1);
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
ij> -- nested joins
select * from t1 join t2 on t1.c1 = t2.c1 inner join t3 on t1.c1 = t3.c1;
C1         |C1         |C1         
-----------------------------------
3          |3          |3          
ij> -- parens
select * from (t1 join t2 on t1.c1 = t2.c1) inner join t3 on t1.c1 = t3.c1;
C1         |C1         |C1         
-----------------------------------
3          |3          |3          
ij> select * from t1 join (t2 inner join t3 on t2.c1 = t3.c1) on t1.c1 = t2.c1;
C1         |C1         |C1         
-----------------------------------
3          |3          |3          
ij> -- [inner] joins
select * from t1 a left outer join t2 b on a.c1 = b.c1 inner join t3 c on b.c1 = c.c1;
C1         |C1         |C1         
-----------------------------------
3          |3          |3          
ij> select * from (t1 a left outer join t2 b on a.c1 = b.c1) inner join t3 c on b.c1 = c.c1;
C1         |C1         |C1         
-----------------------------------
3          |3          |3          
ij> select * from t1 a join t2 b on a.c1 = b.c1 inner join t3 c on c.c1 = a.c1 where c.c1 > 2 and a.c1 > 2;
C1         |C1         |C1         
-----------------------------------
3          |3          |3          
ij> select * from (t1 a join t2 b on a.c1 = b.c1) inner join t3 c on c.c1 = a.c1 where c.c1 > 2 and a.c1 > 2;
C1         |C1         |C1         
-----------------------------------
3          |3          |3          
ij> select * from t1 a join (t2 b inner join t3 c on c.c1 = b.c1) on a.c1 = b.c1 where c.c1 > 2 and b.c1 > 2;
C1         |C1         |C1         
-----------------------------------
3          |3          |3          
ij> -- test insert/update/delete
insert into insert_test
select * from t1 a join t2 b on a.c1 = b.c1 inner join t3 c on a.c1 <> c.c1;
7 rows inserted/updated/deleted
ij> select * from insert_test;
C1         |C2         |C3         
-----------------------------------
1          |1          |2          
1          |1          |3          
1          |1          |5          
1          |1          |7          
3          |3          |2          
3          |3          |5          
3          |3          |7          
ij> update insert_test
set c1 = (select 9 from t1 a join t1 b on a.c1 = b.c1 where a.c1 = 1)
where c1 = 1;
4 rows inserted/updated/deleted
ij> select * from insert_test;
C1         |C2         |C3         
-----------------------------------
9          |1          |2          
9          |1          |3          
9          |1          |5          
9          |1          |7          
3          |3          |2          
3          |3          |5          
3          |3          |7          
ij> delete from insert_test
where c1 = (select 9 from t1 a join t1 b on a.c1 = b.c1 where a.c1 = 1);
4 rows inserted/updated/deleted
ij> select * from insert_test;
C1         |C2         |C3         
-----------------------------------
3          |3          |2          
3          |3          |5          
3          |3          |7          
ij> -- multicolumn join
select * from insert_test a join insert_test b 
on a.c1 = b.c1 and a.c2 = b.c2 and a.c3 = b.c3;
C1         |C2         |C3         |C1         |C2         |C3         
-----------------------------------------------------------------------
3          |3          |2          |3          |3          |2          
3          |3          |5          |3          |3          |5          
3          |3          |7          |3          |3          |7          
ij> -- continue with insert tests
delete from insert_test;
3 rows inserted/updated/deleted
ij> insert into insert_test
select * from (select * from t1 a join t2 b on a.c1 = b.c1 inner join t3 c on a.c1 <> c.c1) d (c1, c2, c3);
7 rows inserted/updated/deleted
ij> select * from insert_test;
C1         |C2         |C3         
-----------------------------------
1          |1          |2          
1          |1          |3          
1          |1          |5          
1          |1          |7          
3          |3          |2          
3          |3          |5          
3          |3          |7          
ij> delete from insert_test;
7 rows inserted/updated/deleted
ij> -- reset autocomiit
autocommit on;
ij> -- drop the tables
drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table insert_test;
0 rows inserted/updated/deleted
ij>