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