Outer Join

An outer join does not require each record in the two join tables to have a matching record.  

An outer join returns rows depending on the join condition (ON).

The resulting data set can keep each records from the joined tables even if no other matching record exists.

In the FROM clause, the left table appears to the left of the OUTER JOIN keywords, and the right table appears to the right of these keywords.

The left table is also called the outer table, and the right table is also called the inner table.

For example

In the following statement the employees table is the LEFT or OUTER table:

SELECT employee_id, last_name, first_name

FROM   employee LEFT OUTER JOIN department

ON     (employee.department_id=department.department_id);

Outer joins require the outer joined table to be the driving table. In the preceding example, employees is the DRIVING table, and departments is the DRIVEN-TO table.

The driving table keeps each record even if no other matching record exists.

Types of Outer Join

There are different join conditions or types in Outer join -

1. LEFT outer join

2. RIGHT outer join

3. FULL outer join

For Example, we have two tables Employee and Location.

Table -  Employee

Employee_id	First_Name
13	Jason
8	Alex
3	Ram
17	Babu
25	Johnson

Table -  Location

Employee_id	Location
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
39	Bangalore

1. LEFT outer join

SELECT employee.employee_id,location.location 

FROM employee 

LEFT OUTER JOIN location 

ON employee.employee_id = location.employee_id;

Result Set -

Employee_id	Location
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
25

So, a left outer join retains all of the rows of the LEFT/DRIVING table, regardless of whether there is a row that matches on the RIGHT/DRIVEN-TO table.
 


2. RIGHT outer join

SELECT employee.employee_id,location.location 

FROM employee 

RIGHT OUTER JOIN location 

ON employee.employee_id = location.employee_id;

Result Set - 

Employee_id	Location
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
	Bangalore

So, a right outer join retains all of the rows of the RIGHT/DRIVING table, regardless of whether there is a row that matches on the LEFT/DRIVEN-TO table. 

 
3. FULL outer join

SELECT employee.employee_id,location.location 

FROM employee 

FULL OUTER JOIN location 

ON employee.employee_id = location.employee_id;

Result Set -

Employee_id	Location
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
25
	Bangalore

Q: What is the difference between the right and left outer joins?

The difference is simple – in a LEFT OUTER JOIN, all of the rows from the LEFT table will be displayed, regardless of whether there are any matching columns in the RIGHT table. In a right outer join, all of the rows from the RIGHT table will be displayed, regardless of whether there are any matching columns in the LEFT table.

Q: What is the difference between the "Left Join" and "Left outer Join"?

No difference actually!

Suppose we have a table A and a table B. 
So. here a list of equivalent Join syntax:

A LEFT JOIN B    =   A LEFT OUTER JOIN B  
A RIGHT JOIN B  =   A RIGHT OUTER JOIN B
A FULL JOIN B    =   A FULL OUTER JOIN B  
A JOIN B              =   A INNER JOIN B