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:

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;

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.

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:

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.