create table left_t (	 
  join_column number,  
  left_c2     varchar2(10),  
  left_c3     integer 
)

create table right_t  (	 
  join_column number,  
  right_c2    varchar2(10),  
  right_c3    integer 
)

begin 
  insert into left_t (join_column,left_c2,left_c3)  
    values (1,'LEFT',1); 
  insert into left_t (join_column,left_c2,left_c3)  
    values (2,'LEFT',2); 
  insert into right_t (join_column,right_c2,right_c3)  
    values (2,'RIGHT',1); 
  insert into right_t (join_column,right_c2,right_c3)  
    values (2,'RIGHT',2); 
  insert into right_t (join_column,right_c2,right_c3)  
    values (3,'RIGHT',3); 
end; 

Cross join - Cartesian product

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
cross  join right_t rt  
order  by left_c3, right_c3 

Inner join - rows matching join criteria in both tables

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
inner  join right_t rt  
on     lt.join_column = rt.join_column  
order  by left_c3, right_c3 

Inner join with many join columns

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
inner  join right_t rt  
on     lt.join_column = rt.join_column  
and    lt.left_c3 = rt.right_c3  
order  by left_c3, right_c3 

Band join - rows matching range criteria

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
inner  join right_t rt  
on     lt.join_column between rt.right_c3 and rt.join_column  
order  by left_c3, right_c3 

Non-equijoin - non-matching rows from both tables

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
inner  join right_t rt  
on     lt.join_column <> rt.join_column  
order  by left_c3, right_c3 

Left outer join - return all rows from outer (left) table

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
left   join right_t rt  
on     lt.join_column = rt.join_column  
order  by left_c3, right_c3 

Right outer join - return all rows from outer (right) table

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
right  join right_t rt  
on     lt.join_column = rt.join_column  
order  by left_c3, right_c3 

Outer join - filtering inner table in where makes this an inner join

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
left   join right_t rt  
on     lt.join_column = rt.join_column  
where  right_c3 > 1  
order  by left_c3, right_c3 

Outer join - to filter the inner table this must be in join clause

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
left   join right_t rt  
on     lt.join_column = rt.join_column  
and    right_c3 > 1  
order  by left_c3, right_c3 

Partitioned outer join - return all rows from outer table with every value for the partition columns

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
left join right_t rt  
  partition by ( right_c3 )  
on     lt.join_column = rt.join_column  
order  by left_c3, right_c3 

Full outer join - every row from both tables

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
full   join right_t rt  
on     lt.join_column = rt.join_column  
order  by left_c3, right_c3 

Cross apply - join in subquery

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
cross  apply (   
  select * from right_t rt  
  where  lt.join_column = rt.join_column  
  order  by right_c3  
  fetch first 1 rows only  
) rt  
order  by left_c3, right_c3 

Outer apply - join in subquery preserving outer (left) table

select lt.join_column left_join_c, left_c2, left_c3,  
       rt.join_column right_join_c, right_c2, right_c3  
from   left_t lt  
outer  apply (   
  select * from right_t rt  
  where  lt.join_column = rt.join_column  
  order  by right_c3  
  fetch first 1 rows only  
) rt  
order  by left_c3, right_c3