Left Join versus Right Join Comparison

I recently put together a lesson on the difference between left join versus right join operators.  Outer joins are a great way to match tables, while allowing still including rows from one table that don’t necessarily match to another table.

The good news is that when it comes to right outer joins or, its twin, the left outer join, there isn’t much of a difference to learn.   When it comes to the right vs left outer join debate, it is more about point-of-view.

You can learn more about this this in the  video above, it’s transcript follows

What is the Difference Between a Right Outer Join and Left Outer Join Transcript

Welcome back.  In this lesson we’re going to learn the difference between a right and left join.

The difference between left and right outer joins has to do with table position.  A left and right refer to where a table resides in relationship to the FROM clause.  The left table is the table that is in the FROM clause, or left of the join condition, the join clause here.  And a right table is on the right side of the join clause.

When we speak of a left outer join, what we’re saying is, take all the rows from the left table, and join them to rows on the right table.  And conversely, when we start learning about right outer joins, what we’ll be saying is take all the rows from the right table, and match them where you can to rows from the left table.

IF you want to learn about other join types, then SQL Joins – The Ultimate Guide is a good read.

How Left Joins Work

Let’s go dig into how both joins work.  Let’s start with a SQL left join.  Here we have product, sales, and results.

This is similar to what we had in our introduction.  I did add Fred Orr as another example to help us with our joins.  And, as you recall from our introduction, we have a query for left outer join, so we’re now pulling in the ProductName, the Sales.CustomerName from product, doing a left outer join on sales, using the product ID as our match. 

We’re going to bring in the name, the customer to bring in results.

In this case where the product ID matches, it’s very straight forward.  It acts just like an inner join, and we pull in matches for John Smith.

In the case of the outer join where blender, product ID=2, does not have a match in the sales table.  We get blender, and then NULL.  This goes back to the case where, in a left outer join, we’re seeing match all products from the left, and where we can, pull in rows from the right.

Well, here’s a case where we really can’t pull in a row from the right.  We bring in a NULL value.

How Right Joins Work

Let’s now look at a right outer join.  In the SQL right join you can see that our statement changed ever so slightly.  In fact, all we changed is the wording, left to right, right here in the statement.

How’s this going to work?

Well, in the case of the mixer for John Smith, things work just the same.  When we pull in the mixer for John Smith it’s going to pull in the results for John Smith.

However, for the blender, you’ll notice, for sales there is no blender number two option now, is there? what do we do for results here? You’ll notice that blender doesn’t even show up in the results.

Sales is now driving what rows show up in our results table, and blender isn’t even appearing.

You’ll see where we have the name John Smith for mixer, because that matched.  Of course, Mary Howe with the chopper, row three.  And then, notice Fred Orr is a customer.

He’s a customer that never bought anything.  we have a customer with no product.  And then, of course, there’s this product that has no customer.  It does not show up, because, in our case here, it’s not part of the scheme of match everything from sales, and then try to match a product.

The right outer join is pulling in every row from sales, and then trying to match something from product.  that’s why the result is showing every row from sales, and then there’s a blank for Fred Orr.

Comparison of Outer Joins

Here’s a comparison really, of what we’ve all talked about.  Check out the product table, it contains mixer, blender, chopper.  There there are the customer. Notice the left outer join statement, and the right outer join statement; the only difference is is that the left outer join has left, and the right outer join we just changed the word to right.

The key on the results is subtle, but very important.  What I want you to understand, and study here is that the results on the left outer join are all dictated by product, because we said, “Scan the product table, and where possible match something from sales.” if you were to go down the product table you’d find mixer, blender, chopper, and so on.  And here I have mixer, blender, chopper, and so on.  I have the three results, and that’s it, and for blender there’s nothing, because there was nothing in sales.

Right Join Comparison

When I go to the right outer join notice how the results are being driven entirely by the rows from the sales table.  you’ll see John Smith, Mary Howe, Fred Orr, John Smith, Mary Howe, Fred Orr.

Where possible I’d match across product to try to get the corresponding product that that person bought.  We know that John bought a mixer, Mary bought a chopper, Fred bought nothing.  There’s a null value.

Let’s go do some examples.  Here’s the example of a right outer join.  In this join I’m pulling in information from the product vendor table.  And, for our purposes, we’re going to bring in just the product ID, and the name.

Since this is a right outer join, we should expect to see all vendors listed, and then where possible the products that they supply.  If there are no products they supply we’ll still see the vendor, and then the product ID will be null.

I’m going to just  to add some little flare here, I’m going to add another column, because this looks boring.  let’s do PV, and then add …  Let’s see what field we want to add.  We’ll do standard price.  this will give us the product ID, and the standard price for this product.

It will give us the name of all the vendors, and then the products they supply.  And you may find that there are some vendors that do not supply products.  I’m not sure.  What I could do to check if there are people that aren’t supplying a product is do a where PV.product ID is null.  And what this would allow me to do then is definitively check my results to see if there are any vendors that we have in our system who are not currently supplying a product.

And here they are.  I have several suppliers out here, or vendors, and there’s no product that they currently are listed for.

This can be a really good technique to explore referential integrity.  And I have a couple more examples further on in the course that explore this.

But this is a good way of using outer joins, because it lets you explore the relationship between two tables, and then see where the matches, in a sense, fail.  what we’re doing here is we’re saying, “Joined vendor on product vendor.” And where you see a failure, meaning where business entity ID isn’t matching, so we aren’t finding our vendor in a list of product vendors, that could be a failure in a sense, then let us see that, and show the list of those vendors.

Here’s the list of those vendors.  these are the vendors that haven’t had a product listed yet in our system.  That could either be a good thing, or a bad thing depending on how your business processes are set up.  Perhaps, these are just new vendors and we haven’t set up their products yet.

Left Join Comparison

Okay, so now let’s take the same query and show it as a left outer join.  And again, all I need to do is change the wording from left to right.  I’m going to create a new query though, just to save that one.

Okay, so here’s my copy of my query.  Now I’m going to make it a left outer join.  Bear in mind, just changing the word from right to left give the query an entirely different meaning.  now what we are doing is looking for all product vendors that we have in our system that may not have a listed vendor.

This could be a more serious issue.  Because what we’re saying is that we actually have a product in our system, and we’re thinking that we have a vendor listed, but for some reason that business entity ID, that would be referencing that vendor, doesn’t exists, and that would be a referential integrity error.

We are relying on the fact that product vendor is related to vendor through the business entity ID.  And now that we have a business entity ID and product vendor, but in vendor we’ve broken that relationship.

let’s run this query and see what it looks like.  I do get rows back.  Again, I’m getting quite a few rows.  I think the quickest thing here to do to see if there might be a referential issue would be to add a where clause like we did before.  This is a great technique to be able to check your data.  let’s do a where V dot, and then we’ll do business entity ID is null, because if we were to look at …  I’ll put that here too, just in case.  If it does come back we can see it displayed.

Now, I’m not expecting to see any issues.  I think our data’s in good shape, but it could be such that there is an issue.  No issues.  But again, this is a really good way to be able to check your database to make sure that all your relationships are in place.

Hopefully, now you have a good understand between the difference of left, and right outer joins.  Remember that the left table is referring to the table in the FROM clause, and the right table is the table that is after the join clause.  The key difference between a left outer join, and a right outer join is that in a left outer join it’s the table in the FROM clause whose all rows are returned.  Whereas, in a right outer join we are returning all rows from the table specified in the join clause.

See you in the next lesson.