Using Full Outer Joins
Try reloading this page, or reviewing your browser settings
This video explains how to use Right Outer Joins to retrieve columns from multiple tables.
- Inner Joins
- Multiple Tables
- Outer Joins
- Right Join
- Child Table
- Orphan Records
About this video
- John Deardurff
- First online
- 05 April 2019
- Online ISBN
- Copyright information
- © John Deardurff 2019
Hello. Join me as we talk about using right outer joins. Previously, we have talked about our other join types. But for this video segment, we’re going to focus on right outer joins, or for our example, if I would like to find all my orders that have a customer or do not have a customer. So if we look at our code, you could see I have typed in right outer join instead of left outer join or inner join. And I’ve also changed in our select statement from using customer ID from the parent table to use CustID from the child table. So we can actually see those values.
In a results set, we can see that we have two orders that do not have a last name for customer ID 290 and customer ID 402. These are considered orphan records. They’re orphan records because they are records in the child table, but do not have a matching value in the customer’s table.
Now, in most cases, when our tables are created with our relationships, referential integrity is enforced. What that means is if I have referential integrity enforced, it will not allow me to add, modify, or delete records in a child table if a matching value is not present in the parent table. This is by default. However, developers or database designers can change the relationship to allow those orphan records if the database needs that. So that’s where you would use right outer joins if you ever need to look for those types of records.
So let’s jump into our code. As you can see, previously, we wrote a left outer join to show all the records from our parent table whether they had a matching customer or not. But now we’re going to look for our orphan records. So I changed this from a left outer join to a right outer join. And now we can see in our rows set that they have a customer ID and last name from the customers table that have null values, which means they have no value in the customer table, in the parent table.
Now, if I did want to see the customer ID value, I would have to change the value in our select list to CustID to retrieve that column from the orders tables. So when they execute– so now you can see that value is being pulled from the orders table or the child table.
Now, again, if I was only looking for orphan records or only the records from the right side or the child side, I can use a where statement again and look for where last name is null. And that would just show me the two records that did not have a matching value in the parent table, or in this case, did not have a last name value in the customers table. So in this video segment, we have discussed how to use right outer joins.