Difference between Left, Right and Full Outer Join

Database Management System (DBMS) allows retrieving data from more than one table using joins.

Joins are mainly Cartesian product of two or more relations (or tables).

SQL Joins are broadly categorized as Inner Join and Outer Join. Inner Join selects rows from the tables that fulfills the join condition. But using inner join the data specifically the rows from both the tables that do not satisfy the condition are lost. Outer Join can be used to prevent the loss of data from the tables.

Types of Outer Join :
Outer join is again classified into 3 types: Left Outer Join, Right Outer Join, and Full Outer Join. These are explained as following below.

1. Left Outer Join:
   Left Outer Join returns all the rows from the table on the left and columns of the table on the right is null padded. Left Outer Join retrieves all the rows from both the tables that satisfy the join condition along with the unmatched rows of the left table.

   Syntax:

   SELECT [column1, column2, ....]
   FROM   table1
   
   LEFT OUTER JOIN table2 ON 
   table1.matching_column = table2.matching_column
   
   WHERE [condition]; 

   Or

   SELECT [column1, column2, ....]
   FROM   table1
   
   LEFT OUTER JOIN table2 
   ON table1.matching_column = table2.matching_column
   
   WHERE [condition]; 

   Diagrammatic Representation :

   

2. Right Outer Join:
   Right Outer Join returns all the rows from the table on the right and columns of the table on the left is null padded. Right Outer Join retrieves all the rows from both the tables that satisfy the join condition along with the unmatched rows of the right table.

   Syntax:

   SELECT [column1, column2, ....]
   FROM   table1
   
   RIGHT OUTER JOIN table2 ON 
   table1.matching_column = table2.matching_column
   
   WHERE [condition]; 

   Or,

   SELECT [column1, column2, ....]
   FROM   table1
   
   RIGHT OUTER JOIN table2 
   ON table1.matching_column(+) = table2.matching_column
   
   WHERE [condition]; 

   Diagrammatic Representation :

   

3. Full Outer Join:
   Full Outer Join returns all the rows from both the table. When no matching rows exist for the row in the left table, the columns of the right table are null padded. Similarly, when no matching rows exist for the row in the right table, the columns of the left table are null padded. Full outer join is the union of left outer join and right outer join.

   Syntax:

   SELECT [column1, column2, ....]
   FROM   table1
   
   FULL OUTER JOIN table2 
   ON table1.matching_column = table2.matching_column
   
   WHERE [condition]; 

   Diagrammatic Representation :

   

Example:
Consider following employee table,

EMPID	ENAME	EMPDEPT	SALARY
101	Amanda	Development	50000
102	Diana	HR	40000
103	Bruce	Designing	30000
104	Steve	Testing	35000
105	Roger	Analyst	10000

Department Table :

DEPTID	DEPTNAME	LOCATION
10	Development	New York
11	Designing	New York
12	Testing	Washington
13	HelpDesk	Los Angeles

Now,
1. Left Outer Join query –

Select empid, ename, deptid, deptname 
from employee 
left outer join department 
on employee.empdept = department.deptname; 

Output:

EMPID	ENAME	DEPTID	DEPTNAME
101	Amanda	10	Development
103	Bruce	11	Designing
104	Steve	12	Testing
102	Diana	null	null
105	Roger	null	null

2. Right Outer Join query –

Select empid, ename, deptid, deptname 
from employee right outer join department 
on employee.empdept = department.deptname;

EMPID	ENAME	DEPTID	DEPTNAME
101	Amanda	10	Development
103	Bruce	11	Designing
104	Steve	12	Testing
null	null	13	HelpDesk

3. Full Outer Join query –

Select empid, ename, deptid, deptname 
from employee full outer join department 
on employee.empdept = department.deptname;

EMPID	ENAME	DEPTID	DEPTNAME
101	Amanda	10	Development
103	Bruce	11	Designing
104	Steve	12	Testing
102	Diana	null	null
105	Roger	null	null
null	null	13	HelpDesk

Differences between Left Outer Join, Right Outer Join, Full Outer Join :

Left Outer Join	Right Outer Join	Full Outer Join
Fetches all the rows from the table on the left	Fetches all the rows from the table on the right	Fetches all the rows from both the tables
Inner Join + all the unmatched rows from the left table	Inner Join + all the unmatched rows from the right table	Inner Join + all the unmatched rows from the left table + all the unmatched rows from the right table
Unmatched data of the right table is lost	Unmatched data of the left table is lost	No data is lost
SELECT [column1, column2, ….] FROM table1 LEFT OUTER JOIN table2 ON table1.matching_column = table2.matching_column	SELECT [column1, column2, ….] FROM table1 RIGHT OUTER JOIN table2 ON table1.matching_column = table2.matching_column	SELECT [column1, column2, ….] FROM table1 FULL OUTER JOIN table2 ON table1.matching_column = table2.matching_column

My Personal Notes arrow_drop_up

Save