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.
- 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 :
- 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 :
- 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
|
Last Updated :
03 May, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...