Joins in Oracle – 101

For anyone who is starting with relational databases (using for example Oracle) understaning Table Joins (or result sets) is one of the most daunting and confusing concept. Nevertheless, Join operation is a fundamental operation in RDBMS.In this writeup I’ll attempt to describe a simple, empircal approach to get an understaning of joins in relational database. Specifically I’ll use Oracle as platform, forI believe the join constructs supported in Oracle is fairly comprehensive (even for theoritical concepts).
Available online documentation on Table Joins in relational database is exhaustive.
The approach (and sample code) included in this writeup eliminates any implicit meaning and lets you concentrate on RDBMS implmentation of Table Join operation.

If you have access to Oracle DBMS, you can cut n’ paste the code samples and give it try and get a feel!

Required setup

We’ll set up few tables and sample data for our experiments…

-- SETUP for examples... 
drop table A; 
drop table B; 
drop table C;   
create table a (ca int, sa varchar2(50)); 
create table b (cb int, sb varchar2(50)); 
create table c (cc int, sc varchar2(50));   
insert into A 
select * from ( select level myid, to_char( to_date( level,'J'),'Jsp') mystr 
from dual connect by level < 11 ); 
commit;   
insert into B select * from ( select level myid, to_char( to_date( level,'J'),'Jsp') mystr 
from dual connect by level < 21 ) where mod(myid,2) = 0; 
commit;   
insert into C 
select * from ( select level myid, to_char( to_date( level,'J'),'Jsp') mystr 
from dual connect by level < 11 ) where myid < 6; 
commit;

Let us verify the data, we have setup.

-- Verify the setup 
select * from A; 
select * from B; 
select * from C; 

Now let us explore the various JOIN operations and their results in Oracle.
Note: These operations are very similar in other commercial databases like DB2, MySQL etc.
R1: To select rows from table A and matching rows from table B.

-- ************************************************************** 
-- Requirement EQUI JOIN 1. (AKA INNER JOIN) 
-- To select rows from A and MATCHING rows from B 
-- use A JOIN B or B JOIN A  
-- 
-- ANSI SQL syntax 
select * from A JOIN B on (A.CA = B.CB) ; 
-- **** OR **** 
select * from B JOIN A on (A.CA = B.CB) ;
-- OR Traditional Oracle syntax 
select * from A, B where A.CA = B.CB ; 

R2: To select rows from A and MATCHING rows from table B and subsequent MATCHING rows from table C.

-- ANSI SQL syntax 
select * from A JOIN B on (A.CA = B.CB) JOIN C on(B.CB = C.CC) ; 
-- OR Traditional Oracle syntax 
select * from A, B, C 
where A.CA = B.CB and B.CB = C.CC; 

R3: Requirement
OUTER JOIN 1.
To select ALL rows from A and ONLY MATCHING rows from B use A LEFT OUTER JOIN B or B RIGHT OUTER JOIN A. The columns for table B will have NULL value where a match for A is not found.

-- ANSI SQL syntax 
select * from A LEFT OUTER JOIN B on (A.CA = B.CB); 
-- **** OR **** 
select * from B RIGHT OUTER JOIN A on (A.CA = B.CB); 
-- OR Traditional Oracle syntax 
select * from A, B where A.CA = B.CB(+); 

The queries above are EQUIVALENT – return the same result set. However note the “order” in which the rows are returned by default.
OUTER JOIN 2.
To select ALL rows from B and ONLY MATCHING rows from A use B LEFT OUTER JOIN A or A RIGHT OUTER JOIN B. The columns for table A will have NULL value where a match for B is not found.

-- ANSI SQL syntax 
select * from B LEFT OUTER JOIN A on (A.CA = B.CB); 
-- **** OR **** 
select * from A RIGHT OUTER JOIN B on (A.CA = B.CB); 
-- Traditional Oracle syntax 
select * from A, B where A.CA(+) = B.CB; 

OUTER JOIN 3.
To select ALL rows from A and ONLY MATCHING rows from B AND select those rows from C that match rows from B

-- ANSI SQL syntax
select * from A LEFT OUTER JOIN B on (A.CA = B.CB)LEFT OUTER JOIN C on (B.CB = C.CC);
-- OR Traditional Oracle syntax
select * from A, B, C where A.CA = B.CB(+) and B.CB = C.CC(+);

In addition to INNER and OUTER (LEFT and RIGHT) Joins, Oracle also provides (in ANSI extension) FULL OUTER join.
Note: FULL OUTER join operation can be accomplished using traditional SQL (without ANSI extensions).
R4: To select ALL rows from A and ALL rows from B, however match the rows from A and B where possible.