joins.out

derby database source code.good for you.

ij> --
-- this test shows the current supported join functionality
--
-- create some tables
create table t1 (t1_c1 int, t1_c2 char(10));
0 rows inserted/updated/deleted
ij> create table t2 (t2_c1 int, t2_c2 char(10));
0 rows inserted/updated/deleted
ij> create table t3 (t3_c1 int, t3_c2 char(10));
0 rows inserted/updated/deleted
ij> create table t4 (t4_c1 int, t4_c2 char(10));
0 rows inserted/updated/deleted
ij> -- populate the tables
insert into t1 values (1, 't1-row1');
1 row inserted/updated/deleted
ij> insert into t1 values (2, 't1-row2');
1 row inserted/updated/deleted
ij> insert into t2 values (1, 't2-row1');
1 row inserted/updated/deleted
ij> insert into t2 values (2, 't2-row2');
1 row inserted/updated/deleted
ij> insert into t3 values (1, 't3-row1');
1 row inserted/updated/deleted
ij> insert into t3 values (2, 't3-row2');
1 row inserted/updated/deleted
ij> insert into t4 values (1, 't4-row1');
1 row inserted/updated/deleted
ij> insert into t4 values (2, 't4-row2');
1 row inserted/updated/deleted
ij> -- negative test, same exposed name
select * from t1, t1;
ERROR 42X09: The table or alias name 'T1' is used more than once in the FROM list.
ij> -- cartesian products
-- full projection
select * from t1, t2;
T1_C1      |T1_C2     |T2_C1      |T2_C2     
---------------------------------------------
1          |t1-row1   |1          |t2-row1   
1          |t1-row1   |2          |t2-row2   
2          |t1-row2   |1          |t2-row1   
2          |t1-row2   |2          |t2-row2   
ij> select * from t1 a, t2 b, t3 cc, t4 d order by 1,2,3,4,5,6;
T1_C1      |T1_C2     |T2_C1      |T2_C2     |T3_C1      |T3_C2     |T4_C1      |T4_C2     
-------------------------------------------------------------------------------------------
1          |t1-row1   |1          |t2-row1   |1          |t3-row1   |2          |t4-row2   
1          |t1-row1   |1          |t2-row1   |1          |t3-row1   |1          |t4-row1   
1          |t1-row1   |1          |t2-row1   |2          |t3-row2   |2          |t4-row2   
1          |t1-row1   |1          |t2-row1   |2          |t3-row2   |1          |t4-row1   
1          |t1-row1   |2          |t2-row2   |1          |t3-row1   |2          |t4-row2   
1          |t1-row1   |2          |t2-row2   |1          |t3-row1   |1          |t4-row1   
1          |t1-row1   |2          |t2-row2   |2          |t3-row2   |2          |t4-row2   
1          |t1-row1   |2          |t2-row2   |2          |t3-row2   |1          |t4-row1   
2          |t1-row2   |1          |t2-row1   |1          |t3-row1   |2          |t4-row2   
2          |t1-row2   |1          |t2-row1   |1          |t3-row1   |1          |t4-row1   
2          |t1-row2   |1          |t2-row1   |2          |t3-row2   |2          |t4-row2   
2          |t1-row2   |1          |t2-row1   |2          |t3-row2   |1          |t4-row1   
2          |t1-row2   |2          |t2-row2   |1          |t3-row1   |2          |t4-row2   
2          |t1-row2   |2          |t2-row2   |1          |t3-row1   |1          |t4-row1   
2          |t1-row2   |2          |t2-row2   |2          |t3-row2   |2          |t4-row2   
2          |t1-row2   |2          |t2-row2   |2          |t3-row2   |1          |t4-row1   
ij> -- reorder columns
select t2.*, t1.* from t1, t2;
T2_C1      |T2_C2     |T1_C1      |T1_C2     
---------------------------------------------
1          |t2-row1   |1          |t1-row1   
2          |t2-row2   |1          |t1-row1   
1          |t2-row1   |2          |t1-row2   
2          |t2-row2   |2          |t1-row2   
ij> select t2_c2, t1_c2, t1_c1, t2_c1 from t1, t2;
T2_C2     |T1_C2     |T1_C1      |T2_C1      
---------------------------------------------
t2-row1   |t1-row1   |1          |1          
t2-row2   |t1-row1   |1          |2          
t2-row1   |t1-row2   |2          |1          
t2-row2   |t1-row2   |2          |2          
ij> -- project out columns
select t2_c2, t1_c1 from t1, t2;
T2_C2     |T1_C1      
----------------------
t2-row1   |1          
t2-row2   |1          
t2-row1   |2          
t2-row2   |2          
ij> select a.t1_c1, cc.t1_c1, e.t1_c1, g.t1_c1, i.t1_c1 from t1 a, t1 cc, t1 e, t1 g, t1 i;
T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      
-----------------------------------------------------------
1          |1          |1          |1          |1          
1          |1          |1          |1          |2          
1          |1          |1          |2          |1          
1          |1          |1          |2          |2          
1          |1          |2          |1          |1          
1          |1          |2          |1          |2          
1          |1          |2          |2          |1          
1          |1          |2          |2          |2          
1          |2          |1          |1          |1          
1          |2          |1          |1          |2          
1          |2          |1          |2          |1          
1          |2          |1          |2          |2          
1          |2          |2          |1          |1          
1          |2          |2          |1          |2          
1          |2          |2          |2          |1          
1          |2          |2          |2          |2          
2          |1          |1          |1          |1          
2          |1          |1          |1          |2          
2          |1          |1          |2          |1          
2          |1          |1          |2          |2          
2          |1          |2          |1          |1          
2          |1          |2          |1          |2          
2          |1          |2          |2          |1          
2          |1          |2          |2          |2          
2          |2          |1          |1          |1          
2          |2          |1          |1          |2          
2          |2          |1          |2          |1          
2          |2          |1          |2          |2          
2          |2          |2          |1          |1          
2          |2          |2          |1          |2          
2          |2          |2          |2          |1          
2          |2          |2          |2          |2          
ij> -- project/restricts
select a.t1_c1, b.t1_c1, cc.t1_c1, d.t1_c1, e.t1_c1, f.t1_c1, g.t1_c1, h.t1_c1, i.t1_c1, j.t1_c1
from t1 a, t1 b, t1 cc, t1 d, t1 e, t1 f, t1 g, t1 h, t1 i, t1 j
where a.t1_c2 = b.t1_c2 and b.t1_c2 = cc.t1_c2 and cc.t1_c2 = d.t1_c2 and
      d.t1_c2 = e.t1_c2 and e.t1_c2 = f.t1_c2 and f.t1_c2 = g.t1_c2 and
      g.t1_c2 = h.t1_c2 and h.t1_c2 = i.t1_c2 and i.t1_c2 = j.t1_c2;
T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      
-----------------------------------------------------------------------------------------------------------------------
1          |1          |1          |1          |1          |1          |1          |1          |1          |1          
2          |2          |2          |2          |2          |2          |2          |2          |2          |2          
ij> select a.t1_c1, b.t1_c1, cc.t1_c1, d.t1_c1, e.t1_c1, f.t1_c1, g.t1_c1, h.t1_c1, i.t1_c1, j.t1_c1
from t1 a, t1 b, t1 cc, t1 d, t1 e, t1 f, t1 g, t1 h, t1 i, t1 j
where a.t1_c1 = 1 and b.t1_c1 = 1 and cc.t1_c1 = 1 and d.t1_c1 = 1 and e.t1_c1 = 1 and
	  f.t1_c1 = 1 and g.t1_c1 = 1 and h.t1_c1 = 1 and i.t1_c1 = 1 and
	  a.t1_c2 = b.t1_c2 and b.t1_c2 = cc.t1_c2 and cc.t1_c2 = d.t1_c2 and
      d.t1_c2 = e.t1_c2 and e.t1_c2 = f.t1_c2 and f.t1_c2 = g.t1_c2 and
      g.t1_c2 = h.t1_c2 and h.t1_c2 = i.t1_c2 and i.t1_c2 = j.t1_c2;
T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      |T1_C1      
-----------------------------------------------------------------------------------------------------------------------
1          |1          |1          |1          |1          |1          |1          |1          |1          |1          
ij> -- project out entire tables
select 1, 2 from t1, t2;
1          |2          
-----------------------
1          |2          
1          |2          
1          |2          
1          |2          
ij> select 1, t1.t1_c1 from t1, t2;
1          |T1_C1      
-----------------------
1          |1          
1          |1          
1          |2          
1          |2          
ij> select t2.t2_c2,1 from t1, t2;
T2_C2     |2          
----------------------
t2-row1   |1          
t2-row2   |1          
t2-row1   |1          
t2-row2   |1          
ij> -- bug #306
select c.t1_c1 from (select a.t1_c1 from t1 a, t1 b) c, t1 d where c.t1_c1 = d.t1_c1;
T1_C1      
-----------
1          
1          
2          
2          
ij> -- create a table for testing inserts
create table instab (instab_c1 int, instab_c2 char(10), instab_c3 int,
		     instab_c4 char(10));
0 rows inserted/updated/deleted
ij> -- insert select with joins
-- cartesian product
insert into instab select * from t1, t2;
4 rows inserted/updated/deleted
ij> select * from instab;
INSTAB_C1  |INSTAB_C2 |INSTAB_C3  |INSTAB_C4 
---------------------------------------------
1          |t1-row1   |1          |t2-row1   
1          |t1-row1   |2          |t2-row2   
2          |t1-row2   |1          |t2-row1   
2          |t1-row2   |2          |t2-row2   
ij> delete from instab;
4 rows inserted/updated/deleted
ij> insert into instab (instab_c1, instab_c2, instab_c3, instab_c4)
	select * from t1, t2;
4 rows inserted/updated/deleted
ij> select * from instab;
INSTAB_C1  |INSTAB_C2 |INSTAB_C3  |INSTAB_C4 
---------------------------------------------
1          |t1-row1   |1          |t2-row1   
1          |t1-row1   |2          |t2-row2   
2          |t1-row2   |1          |t2-row1   
2          |t1-row2   |2          |t2-row2   
ij> delete from instab;
4 rows inserted/updated/deleted
ij> insert into instab (instab_c1, instab_c2, instab_c3, instab_c4)