Using Self Joins
Try reloading this page, or reviewing your browser settings
This video explains how to use Full Outer Joins to retrieve columns from multiple tables.
- Inner Joins
- Multiple Tables
- Outer Joins
- Right Join
- Child Table
- Full Outer Join
About this video
- John Deardurff
- First online
- 05 April 2019
- Online ISBN
- Copyright information
- © John Deardurff 2019
Welcome back. In this video segment, we’re going to discuss using full outer joins. Now, full outer joins are not used as frequently as an inner join or left join or right outer join. But there might be cases who do need to return all the records from both tables. It could be if you’re doing auditing of your data. Or it could be if you want to archive your data, or possibly do an extract, transform, or load application.
So to see it in code, all you need to do is replace the word left outer join or right outer join with the word full outer join. Now, I’ve also included both the Customer ID field and the Cust ID field from the two tables. That way, we can see where the values match. For example, for Andy Anderson, you can see that there is a 250 from the parent table and a 250 in the Cust ID field from the child table or the Orders table. But for Bob Smith, he only has a value from the customer ID, that 267. But he does not have a matching value from the Orders table in the Cust ID field– same thing with Payton Wayne, Sandy Summerstone, and Penny Lane.
Since we’re doing a full order join, we also see our two orphan records down at the bottom of our results set where they do have a value in the Cust ID field from the child table, but they do not have a matching value in the Customer ID field. So again, a full outer join could be very beneficial if I’m trying to do auditing or trying to track down data errors.
So let’s go take a look at our code. Here, you can see we’ve previously written a right outer join to return our orphan records. So now, I want to replace the word right with the word full. And I do want to add in the Customer ID field from the parent table, from the Customers table, so we can see the matching values.
So I’ll go ahead and execute my code. And now, you can see we do have the matching values for Andy Anderson. So that’s very similar to the inner join. So if we look at Bob Smith, we do have the value of 267 from the Customers table. But we do not have a matching value in the Cust ID field from the Orders table.
And if we scroll down, we can see that the same case can be made for Payton Wayne, Sandy Summerstone, and Penny Lane, where they have a value in the customer ID field from the customer’s table but they do not have a matching record in the Cust ID field from the Orders table. And if I scroll down a little bit more, we can see our two orphan records from the Orders table.
So in this video segment, we’ve discussed how to use full outer joins.