outerjoin.out

derby database source code.good for you.

ij> -- 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;
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
ij> select x.* from x left outer join y on x.c3 = y.c3;
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
4          |5          |6          
ij> select x.* from x right outer join y on x.c3 = y.c3;
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
NULL       |NULL       |NULL       
ij> select y.* from x join y on x.c3 = y.c3;
C3         |C4         |C5         
-----------------------------------
3          |4          |5          
ij> select y.* from x left outer join y on x.c3 = y.c3;
C3         |C4         |C5         
-----------------------------------
3          |4          |5          
NULL       |NULL       |NULL       
ij> select y.* from x right outer join y on x.c3 = y.c3;
C3         |C4         |C5         
-----------------------------------
3          |4          |5          
666        |7          |8          
ij> -- * will return all of the columns of all joined tables
select * from x join y on x.c3 = y.c3;
C1         |C2         |C3         |C3         |C4         |C5         
-----------------------------------------------------------------------
1          |2          |3          |3          |4          |5          
ij> select * from x left outer join y on x.c3 = y.c3;
C1         |C2         |C3         |C3         |C4         |C5         
-----------------------------------------------------------------------
1          |2          |3          |3          |4          |5          
4          |5          |6          |NULL       |NULL       |NULL       
ij> select * from x right outer join y on x.c3 = y.c3;
C1         |C2         |C3         |C3         |C4         |C5         
-----------------------------------------------------------------------
1          |2          |3          |3          |4          |5          
NULL       |NULL       |NULL       |666        |7          |8          
ij> commit;
ij> -- test outer join -> inner join xform
delete from tt1;
5 rows inserted/updated/deleted
ij> delete from tt2;
5 rows inserted/updated/deleted
ij> delete from tt3;
5 rows inserted/updated/deleted
ij> insert into tt1 values (1, 2, 3), (2, 3, 4), (3, 4, 5);
3 rows inserted/updated/deleted
ij> insert into tt2 values (1, 2, 3), (2, 3, 4), (3, 4, 5);
3 rows inserted/updated/deleted
ij> insert into tt3 values (1, 2, 3), (2, 3, 4), (3, 4, 5);
3 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 4500;
ij> -- no xform, predicate on outer table
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt1.c1 = 3;
C1         |C2         |C3         |C1         |C2         |C3         
-----------------------------------------------------------------------
3          |4          |5          |2          |3          |4          
ij> -- various predicates on inner table
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c2 = 3;
C1         |C2         |C3         |C1         |C2         |C3         
-----------------------------------------------------------------------
3          |4          |5          |2          |3          |4          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- various predicates on inner table
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c2 = 3
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 1
Rows seen from the right = 1
Rows filtered = 0
Rows returned = 1
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
Left result set:
	Table Scan ResultSet for TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
	Number of opens = 1
	Rows seen = 1
	Rows filtered = 0
	Fetch Size = 16
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		next time in milliseconds/row = 0
	scan information: 
		Bit set of columns fetched=All
		Number of columns fetched=3
		Number of pages visited=1
		Number of rows qualified=1
		Number of rows visited=8
		Scan type=heap
		start position: 
null		stop position: 
null		qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
	Table Scan ResultSet for TT2 at read committed isolation level using instantaneous share row locking chosen by the optimizer
	Number of opens = 1
	Rows seen = 1
	Rows filtered = 0
	Fetch Size = 16
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		next time in milliseconds/row = 0
	scan information: 
		Bit set of columns fetched=All
		Number of columns fetched=3
		Number of pages visited=1
		Number of rows qualified=1
		Number of rows visited=8
		Scan type=heap
		start position: 
null		stop position: 
null		qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= tt2.c2;
C1         |C2         |C3         |C1         |C2         |C3         
-----------------------------------------------------------------------
2          |3          |4          |1          |2          |3          
3          |4          |5          |2          |3          |4          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= tt2.c2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
Source result set:
	Nested Loop Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 3
	Rows seen from the right = 2
	Rows filtered = 0
	Rows returned = 2
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Left result set:
		Project-Restrict ResultSet (3):
		Number of opens = 1
		Rows seen = 3
		Rows filtered = 0
		restriction = true
		projection = false
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
		Source result set:
			Table Scan ResultSet for TT2 at read committed isolation level using instantaneous share row locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 3
			Rows filtered = 0
			Fetch Size = 16
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				next time in milliseconds/row = 0
			scan information: 
				Bit set of columns fetched=All
				Number of columns fetched=3
				Number of pages visited=1
				Number of rows qualified=3
				Number of rows visited=8
				Scan type=heap
				start position: 
null				stop position: 
null				qualifiers:
None
	Right result set:
		Table Scan ResultSet for TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
		Number of opens = 3
		Rows seen = 2
		Rows filtered = 0
		Fetch Size = 16
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			next time in milliseconds/row = 0
		scan information: 
			Bit set of columns fetched=All
			Number of columns fetched=3
			Number of pages visited=1
			Number of rows qualified=2
			Number of rows visited=24
			Scan type=heap
			start position: 
null			stop position: 
null			qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3;
C1         |C2         |C3         |C1         |C2         |C3         
-----------------------------------------------------------------------
3          |4          |5          |2          |3          |4          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
Source result set: