Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Difference between Left, Right and Full Outer Join

  • Difficulty Level : Medium
  • Last Updated : 03 May, 2020

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).

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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,

EMPIDENAMEEMPDEPTSALARY
101AmandaDevelopment50000
102DianaHR40000
103BruceDesigning30000
104SteveTesting35000
105RogerAnalyst10000

Department Table :



DEPTIDDEPTNAMELOCATION
10DevelopmentNew York
11DesigningNew York
12TestingWashington
13HelpDeskLos Angeles

Now,
1. Left Outer Join query –

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

Output:

EMPIDENAMEDEPTIDDEPTNAME
101Amanda10Development
103Bruce11Designing
104Steve12Testing
102Diananullnull
105Rogernullnull

2. Right Outer Join query –

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

EMPIDENAMEDEPTIDDEPTNAME
101Amanda10Development
103Bruce11Designing
104Steve12Testing
nullnull13HelpDesk

3. Full Outer Join query –

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

EMPIDENAMEDEPTIDDEPTNAME
101Amanda10Development
103Bruce11Designing
104Steve12Testing
102Diananullnull
105Rogernullnull
nullnull13HelpDesk

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

Left Outer JoinRight Outer JoinFull Outer Join
Fetches all the rows from the table on the leftFetches all the rows from the table on the rightFetches 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 lostUnmatched data of the left table is lostNo 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
Recommended Articles
Page :

Start Your Coding Journey Now!