In SQL, what is the difference between a left join and a left outer join?

 

There is actually no difference between a left join and a left outer join – they both refer to the exact same operation in SQL. An example will help clear this up.

Here we have 2 tables that we will use for our example:

Employee Location
EmpID EmpName
13 Jason
8 Alex
3 Ram
17 Babu
25 Johnson
EmpID EmpLoc
13 San Jose
8 Los Angeles
3 Pune, India
17 Chennai, India
39 Bangalore, India

It’s important to note that the very last row in the Employee table does not exist in the Employee Location table. Also, the very last row in the Employee Location table does not exist in the Employee table. These facts will prove to be significant in the discussion that follows.

Left Outer Join

Here is what the SQL for a left outer join would look like, using the tables above:

select * from employee left outer join location 
on employee.empID = location.empID;

Subscribe to our newsletter on the left to receive more free interview questions!

In the SQL above, we actually remove the "outer" in left outer join, which will give us the SQL below. Running the SQL with the “outer” keyword, would give us the exact same results as running the SQL without the “outer”. Here is the SQL without the “outer” keyword:

select * from employee left join location 
on employee.empID = location.empID;

A left outer join (also known as a left join) retains all of the rows of the left table, regardless of whether there is a row that matches on the right table. The SQL above will give us the result set shown below.

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
25 Johnson NULL NULL

What is the difference between a right outer join and a right join?

Once again, a right outer join is exactly the same as a right join. This is what the SQL looks like:

select * from employee right outer join location 
on employee.empID = location.empID;

// taking out the "outer", this would give us
// the same results:

select * from employee right join location 
on employee.empID = location.empID;

Using the tables presented above, we can show what the result set of a right outer join would look like:

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
NULL NULL 39 Bangalore, India

We can see that the last row returned in the result set contains the row that was in the Location table, but not in the Employee table (the "Bangalore, India" entry). Because there is no matching row in the Employee table that has an employee ID of "39", we have NULL’s in the result set for the Employee columns.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.