Outer Joins - Left, Right and Full - Practical Example

Outer Join:

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both). In this case left and right refer to the two sides of the JOIN keyword.

SQL> Select * from A;

emp_id emp_name dpt_id 1001 Saran 20 1002 Jason 20 1003 Brian 20 1004 Paul 10 1012 Mary 40 1007 Steve 30 1008 Jim 20 1009 Mark 20 1010 Yihong 20 1013 Melanie 30 1011 Greathouse 50 1014 Jie 50 1015 Raj 50

SQL> Select * from B;

dpt_id	dpt_name
10	Management
20	Development
30	Usability
40	HR
60	Sales

Left Outer Join:
This means that a left outer join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
SQL> 
select a.EMP_ID, a.EMP_NAME, a.DPT_ID, b.Dpt_name from a 
LEFT OUTER JOIN b on a.DPT_ID = B.DPT_ID 
order by a.EMP_ID;

emp_id	emp_name	dpt_id	dpt_name
1001	Saran	20	Development
1002	Jason	20	Development
1003	Brian	20	Development
1004	Paul	10	Management
1007	Steve	30	Usability
1008	Jim	20	Development
1009	Mark	20	Development
1010	Yihong	20	Development
1011	Greathouse	50	Null
1012	Mary	40	HR
1013	Melanie	30	Usability
1014	Jie	50	Null
1015	Raj	50	Null

Right Outer Join:

This returns all the values from the right table and matched values from the left table or NULL in case of no matching join predicate.
SQL> 
select a.EMP_ID, a.EMP_NAME, a.DPT_ID, b.Dpt_name from a 
RIGHT OUTER JOIN b on a.DPT_ID = B.DPT_ID 
order by a.EMP_ID;

emp_id	emp_name	dpt_id	dpt_name
1001	Saran	20	Development
1002	Jason	20	Development
1003	Brian	20	Development
1004	Paul	10	Management
1007	Steve	30	Usability
1008	Jim	20	Development
1009	Mark	20	Development
1010	Yihong	20	Development
1012	Mary	40	HR
1013	Melanie	30	Usability
Null	Null	Null	Sales

Full Outer Join
a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set.
SQL> 
select a.EMP_ID, a.EMP_NAME, a.DPT_ID, b.Dpt_name from a 
FULL OUTER JOIN b on a.DPT_ID = B.DPT_ID 
order by a.EMP_ID;

emp_id	emp_name	dpt_id	dpt_name
1001	Saran	20	Development
1002	Jason	20	Development
1003	Brian	20	Development
1004	Paul	10	Management
1007	Steve	30	Usability
1008	Jim	20	Development
1009	Mark	20	Development
1010	Yihong	20	Development
1011	Greathouse	50	Null
1012	Mary	40	HR
1013	Melanie	30	Usability
1014	Jie	50	Null
1015	Raj	50	Null
Null	Null	Null	Sales

Regards,
- Akash