We are trying to explain Oracle’s “left outer join”, “right outer join” scenarios with simple examples for beginners. If experts find wrongs with the explanations provided, please forward us correct explanations and we will be more than glad to amend this thread

What you need

2 Tables

Access to  Oracle database (obviously we know you have one )

Execute the following as script (SCOTT/TIGER)

Create table authors
(auth_id number,
auth_name varchar2(60)
);
/
Create table book_titles
(auth_id number,
book_title varchar2(240)
);
/

insert into authors
values(1002,'Abdul Aziz Marafi');
/

insert into authors
values(1100,'Rajesh Thampi');
/

insert into book_titles
values(1006, 'I.T Policies, 2011 Approaches');
/
insert into book_titles
values(1002, 'ASP.3 Switch from ASP');
/
insert into book_titles
values(1112, 'PL/SQL Tricks and Tips');
/
COMMIT;
/
Select 'There are total '||to_char(count(*))||' records in authors table' no_recs from authors;
/

Select  'There are total '||to_char(count(*))||' records in book_titles table' no_recs from book_titles;
/

Once you created tables and inserted rows, let us move to trying out the “left out join” and “right outer join” against our new tables

Right Outer Join Example

/*Here authors table becomes left side table, book_title becomes
right side table and the join condition is trying to fetch all the records which
satisfy auth_id column available in both tables and then all records from left table
even though corresponding condition with table right fails*/

Select a.*, b.book_title from authors a, book_titles b
where
--b.auth_id(+) = a.auth_id  --Uncomment and comment next line to TEST
a.auth_id = b.auth_id(+);

Here table A (authors) is joined with table B (book_titles) against column auth_id and all rows satisfying the condition are fetched first
then balance records from Table A are fetched

This scenario could be once again explained as
bring everything from both tables A,B matching specific condition, then everything from Table A what do not satisfy the join condition
Thus right outer join is could be explained by “us” as “bring everything from left table (A) that are not joinable with a MERE relation expression: eg ‘=’

Left Outer Join Example

Select a.*, b.book_title from authors a, book_titles b
where
b.auth_id = a.auth_id(+) ;

Left outer join matches the condition first, then brings all rows from table B (book_titles) immediately after the condition satisfied rows. Thus left outer join could be explained by “us” as “bring everything from right table (B) that are not joinable with a MERE relative expression: eg ‘=’

Regards,

Admin