Using Left Outer Joins
Try reloading this page, or reviewing your browser settings
This video explains how to use Inner Joins to retrieve columns from multiple tables.
- Inner Joins
- Multiple Tables
About this video
- John Deardurff
- First online
- 05 April 2019
- Online ISBN
- Copyright information
- © John Deardurff 2019
Welcome. In this video segment, we’re going to take a deeper dive into using inner joins. First, let’s take a look at our code. Here, you can see I’ve already written two select statements– one to select the customer ID and last name from the sales.customers table, and one to select the quantity from the sales.orders table.
So if I execute my code, you can see I get two separate results sets– one listing all my customers. And if I scroll down, you’ll see a listing of my quantities from the Orders table. What if I wanted to return those columns from the two tables in a single result set? This is where I would need to use a join.
In our case, we’re going to use an inner join just to show the customers who’ve placed an order. So to write this query, I’m going to remove the select statement here and join the two tables to each other. I’m going to add the Quantity field to the select list. Because now, I can get the Quantity field from the Orders table.
But we’re not quite finished. We need to specify how I’m connecting the two tables. So I can type sales.customers.customerID– I know customer ID is actually in both tables– equals sales.orders.custID. Now, they have two different column names in the two tables that are connecting. But they have the same data type. So I can execute my code. And you can see now I get a single result set of the customers who have placed orders.
Now to simplify writing my code, I can alias my tables as C for the Customers and then as O for the Orders table. And then I would need to modify my on statement to use aliases instead of the full table name. So that will actually save me some typing in the future. So let’s alias that last table. And when I execute my code again, you can see I get the exact same result sets. And I just used a simple join. I could have typed in inner join and returned the same results. So I’ll execute. And you can see, I get the exact same results.
We discussed that inner joins are used to find records that match between a parent table and a child table. In the case of our Customers table and our Orders table, an inner join would return all the records for the customers who’ve placed an order. If we look at our code, where we select the customer ID and last name from the customer’s table and the quantity field from the Orders table, we can see that there are some customers who have placed orders and some customers who have not.
If we look at customer 250 for Andy Anderson, we can see that he has placed three orders. So from our select statement, we are returning those three records because there is a matching value between the two tables. If we look at Jeff Rollins, customer ID of 255, you can he has also placed an order. In that case, his record is being returned in the results set. Bob Smith, 267, does have a record in the Customer table, but does not have a record in the Orders table because Bob Smith has never placed an order. So in this case, we do not see any information about Bob Smith in our results set, because there was a record in the parent table but not one in the child table.
If we look at Ginni Jefferson, 278, she has records in both tables. So her records are returned– same thing with Cindy Samuels, customer ID 388, Larry Daniels, customer ID 433, and Danny Laurels, customer ID 452. You may notice that customer 476, 512, and 526 have not placed any orders. So those results are not showing in the results set. Now, we also have some orders without a matching customer.
In this case, order ID 6 is mapped to a customer ID of 290, but there is not a 290 customer in the Customers table, as well as order ID number 9 has customer ID 402, but there is not a matching customer in the Customers table. These are considered orphan records, which we will work with in more detail when we discuss right outer joins.
So in this video segment, we have discussed how to use inner joins.