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

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.