Using Right Outer Joins
Try reloading this page, or reviewing your browser settings
This video explains how to use Left Outer Joins to retrieve columns from multiple tables.
- Inner Joins
- Multiple Tables
- Outer Joins
- Left Join
- Parent Table
About this video
- John Deardurff
- First online
- 05 April 2019
- Online ISBN
- Copyright information
- © John Deardurff 2019
Hello. Let’s talk about using left outer joins. Previously, we discussed the three different join types. But for this video, we’re going to discuss the left outer join. In this case, we want to return all the records from the parent table, even if there is a matching record or not. For our example, we want to return all the customers, whether or not they’ve placed an order.
So if we look at the code, you can see I’ve replaced inner join with the words, left outer join. And, in this example, we are now returning the values for Bob Smith, Peyton Wayne, Sandy Summerstone, and Penny Layne. They’re not showing up in our records set because the left outer join is returning all the records from the parent table, as well as any of that match. Let’s go look at our code.
Here you can see previously we’ve already written an inner join. Now we’re going to change that inner join into a left outer join. So left outer join. And I’ll go and execute the code. And now you can see we did return Bob Smith, or the customer ID 267, and he has a null for quantity, which means he does not have a value for the quantity because he does not have a record in the Orders table.
And if I scroll down, you could see that our bottom three people– I believe it was Peyton Wayne, Sandy Summerstone, and Mr. Layne– also do not have a value in the quantity field because they are customers who have not placed an order. Now when I do write my left outer join, the outer statement is actually optional. I could just type in left join, and get the exact same result.
So you can see there’s Bob Smith, 267. If I scroll down, there’s Peyton Wayne, Sandy Summerstone and Mr. Layne. Now what if I just want to see the parent records? Well, we can use and Is Null statement in our Where clause. I can say where quantity– quantity is null. And that’s going to show us all the customers who did not place an order. So maybe I want to send them coupons or a discount to get them into our store.
So in this video segment, we’ve discussed how to use left outer joins.