Outer Joins

Video Transcription

Welcome back everyone, this video, we’re gonna be taking a look at our last and final type of join the outer join. So here on the slide here actually shows some of the syntax that we’ve had so far, where we have our FROM clause. And we can join two different tables, two tables to and I say table source, because it doesn’t actually have to be a table from the database, right, it could be the result of a another join as well. So a table of some kind, joined with another table. So we’ve done the cross join, and we’ve done the inner join. And now we are going to do this outer join. This is this last syntax here. So we have a left, right or full outer join. And again, outer is going to be an optional key word, depending on ancy 89, or 92 syntax. But I’m going to encourage that outer be listed there, because it makes your queries significantly easier to read. But let’s take a look at what the OUTER JOIN actually contains with. So an outer join has three logical processing phases, instead of to write. So let’s take a look at what that what that looks like. So we have the Cartesian product, just like what we have with just like what we have with the cross join. And then just like the inner join, we Filter Rows out based off of some predicate. But in addition to that, we can now we now add rows from the preserved table. So what in the world is the preserve table? So fine line here is that the OUTER JOIN is just like an inner join, except with this extra third, this third processing step. So the reserved table is identified by either left, so the left table is preserved, right, or the right table is preserved or full. Both tables are preserved with us. And I’ll cover some more examples of this. Because this initially is kind of weird, like, what do I mean by a table being preserved? Well, I’ll explain that here in just a moment.

Really, we have, again, SQL 89, and 92 syntax, but really, only 92 is going to work in our current version of SQL Server. So having the outer word included there, let’s take a look at some of these joints. So a left join, it looks actually very similar to an inner join instead of although instead of just the this triangle symbol, we now have a tail on whichever side is preserved. So Table A is going to be the table that’s actually preserved. So join table A with Table B. And what do we get as a result? Well, before when we just did an inner join, we only had Kim analysis, right? So first step is cross join. So we have Jim paired with pickles, fish and ice cream, Kim pickles, fish and ice cream and Alice pickles, fish and ice cream. And then we filter based off of the ID. So only the records where the ID matches are kept. So that leaves us with two rows. But then we have a third processing step. So the third processing step is going to then add in the table the rows from the preserved table. So what does that end up with? Well, I get Kim and Alice, which is just like my original inner join. But then I also get gym added back in to the results. Because since I’m doing a Left Outer Join, rows that were not originally matched with the inner join are preserved and the result of the query.

And now you see that I have b.id and food. So B ID is from Table B and food is also from Table B. But those have no values for Jim because Jim net didn’t originally match with any record in table B. And so therefore, even though Jim has preserved in the in the query results, we don’t actually get any values from Table B because again right we had no match for Jim. So, this is the essence of the outer join, and particularly what I mean by a table being preserved. So a the preserve table, whether it be left or the right or both. records that do not have a match from the inner join step will be added back into the query at the end and columns were the the record had no matches for will be no. But let’s take a pause here and take a look at a couple of examples for the left outer join. So I do want to just highlight a couple things just to kind of show just to show the results here. So we have a couple things here, I wanted to show how many customers we actually have so that we have 663 customers in total in our database. And then we also, we also have two different buying groups. And this is important, because I’m going to start to expand this out by joining these two tables together. And so if I cross joined these two, right, it’s essentially the number of rows in table A times the number of rows in table B, and so 663 times two, right?

So if we run this query, aha, so now we get all all all of that. So 13 101 101,326 rows, okay, so each of the buying groups paired with all of the customers. So that is, that’s a lot, right. And that’s not necessarily valid data, it doesn’t really necessarily showcase the relationship between the two. But if we change this to an inner join, instead of just a cross join, okay. So again, right, I’m kind of just showcasing the each of the each of the logical processing steps, right, you can see step one, by you doing just a cross join, you can do, you can see the result of step two by doing just in just the inner join. So with just the inner join, I only get the customers whose buying group matches the customer buying group. And so now I only have 402 rows, right? So only the customers who have a buying group in the buying group table. Now, what if I change this to an outer join? So instead of enter, I’m going to say left. So now what happens?

So if I run this, I had 400. Before. Now I have 663. Again, right? Now I have 663. Again, because right? Because my sales customers is the preserved table, I keep all of the customers. And then I have all the customers who had a match are paired with the record inside of the buying group table. So if we go back over here, and go to the results, if I scroll down aways, let’s see here. Yep, scroll. Scroll down aways here. Now we have all the customers who are not associated with a buying group. So let’s say in this case, these customers aren’t associated with a big company. They’re just, you know, your normal person that is placing some orders for us. But this is the power of what a Left Outer Join can actually achieve. Okay, so one way we can actually identify which which customers are added, so the customers that were added, or the customers who don’t have a buying group. And so we can we can actually figure out the exact same the those exact people by just filtering out those who are no, so were big, buying group and big buying group is no, so the the customers who were not able to be paired with a buying group, those are only those customers. So we have 261 rows, right.

So we had, we had 400 customers who were paired with the buying groups, and then 260, who were not another way to do this is actually adding this based off of the bind group. So we can leave it just as the left outer join. But I’m going to introduce something that’s a little bit more useful as far as the LEFT JOIN goes. So this down here is relatively the same. But now I’m going to actually join a group by the buying group. That way all of the customers with that are associated with buying group, the first one or the second one, so we should have, remember we have to bind groups. So we should have two records as associate two records as a result of this. But remember, the is no function. So that’s going to check if this group Is No, if this column is no, then it’s going to replace no with no buying group, which is a lot more user friendly than saying just no as a result of the table. So this is an extremely useful function to actually have. But let’s give this query run, see what the result is. Haha. So we actually ended up with three buying groups instead of just two. So here are our two original buying groups. And then our third group is the knoll group, right. And remember, when we do a group buy, or aggregates, no oil is treated as the same value. So all no values are considered equivalent. So when we do the group by all Knowles are grouped into the same group, okay, since we’re grouping by buying group, and that’s no, all those who don’t have a buying group get put into that, that no buying group category. So this is pretty useful. Let’s see, though how many customers for each customer category.

But in order to get to the customer category, I’m actually going to need to introduce a right join. So let’s flip back real quick, and see an example of what the right join looks like. So a Right Outer Join, like my symbol here is flipped, right, so the tail is on the right hand side, meaning that the right table table B is going to be the table that’s actually preserved. So Table B will be preserved here. And so I still get Kim and Alice, remember, that’s the result of my inner join. Because Kim, the IDS two and three match with the IDS two and three, and Table B. But now ice cream has no pairing, right. And so the right side of the table, right seven and ice cream are preserved. And the left side where the the a.id, and name are get null out. So they are left out because they have no match. So Jim is not included here and the RIGHT OUTER JOIN, because Table B is preserved instead, instead of Table A. So let’s take a look at an example here. So our right join. I want to see how many customers we have for each category. And so I have this table, let’s actually add this to a second line here. So it’s easier to read. So we have select Customer category ID and category name. And see this is not buying group analysis customer category. But we have count star customer count. And so we have from sales customers, right join sales dot customer categories, on customer category, Id grouped by the category ID and then order by order by the ID. So if we run this, there we go. We have eight rows, so eight customer categories with the customer count.

And each one. We have these weird records here. We have one customer for agent and wholesaler and one for general retailer. So this is kind of weird. Do we actually have one customer in those categories? Or right? Since we have a right join, right, we’re right join on customer categories. And so categories are preserved. So we get all of the categories. But if a category has no customers, the customers are No. and No is still included in account. So we need to do count star, the noise actually kept in play. So we have some unused categories. So we can find those unused categories by changing this out. Let me put this on another line again, we can change this out and add the where customer ID is no. And so if I execute this query, we see those same three categories that how we had one customer and then our have no customer ID associated with them when we do the right join. So how do we correct our queries so that we show zero for the account instead of missing that out? So let’s bring this query back. So here is our query that we had earlier. So we had our count, and here are those three agent, wholesaler and general retailer, all those should have zero customers. Well, instead of counting star counting star will actually force the aggregate to include Knowles by default.

But we can say, See customer ID here instead. And run this query. Ah, now we get truly zero as a result, so count star is going to count the number of rows in the result right in the end that in each group, count customer ID, if you specify the column you want the count, we’ll count the number of non null values, which is an extremely useful, useful tool to make it to do a distinction between. And this is even more important as we start doing left and right and full outer joints. But we can, we can flip this, if we want to do a left join. Right, a left join will actually exclude all the customer categories that don’t match. But if we actually change, just to highlight how the direction now matters with our outer joins, if I’ve switched these two, it’s equivalent to the previous right outer join that we had earlier. But that is the left and right outer join. But if we can do a left outer join, and a right or outer join, we can also do a full outer join. So the FULL OUTER JOIN is going to preserve both tables. So we have tails on both ends now have our symbol there. So a table a full outer join table B. And so we get as a result, right, Kim and Alice, those are the two that have a full set of records, because those are the only two that matched in the inner join. But now we have Jim, on the left, right, because so the the result have a left outer join, and the results of the right outer join. So seven and ice cream are also included.

And you can kind of see the different null values there. But a Full Outer Join is nothing more than the result of a Right Outer Join, and the result of a left outer join. So the preservation step anyways. But let’s take a look at an example of the full outer join. So let’s plot this query. And here is it a run. So just kind of showcasing what this actually is here. So we have color names, stock items, stock item name, now we’re working with the warehouse table. And we’re doing a full join on stock items. On color ID equals color ID order by color ID order by stock ID. And so here, we see we have a bunch of no records. And then if we scroll down enough, we see here’s the two. So all the records up here, that’s the result of the left join. Here’s the right join. And then here are all the records that actually had both so they had to match a color and a stock stock item name. Now, stock item. In general, if talking about the relationship between the table a stock items has a knowable foreign key to the colors table. So and we also have colors that are unused by stock items, right? So imagine have a whole bunch of different colors. And we may not actually have a an item that is that color.

So that is the end kind of the end result of our full outer join. So again, right, a left join is going to preserve the left table, a right join is going to preserve the right side of the join, and then a full preserve both. So just kind of remember highlighting here in general, with all of our difference joins that we’ve covered so far, we have the cross join, which is the all combinations of rows between the two tables, that’s the base join that we’re working off of the inner join adds to the cross join a filter. So give me only the rows that have a match on this particular predicate. So Column A equals column B and so on and so forth. And then when we can also do a OUTER JOIN, which adds a present Step, and the table that is actually preserved is going to be either the left table in the left outer join the right table in the RIGHT OUTER JOIN, or both in case of a full outer join. But that covers the gist of most of the joins we’ll be covering for this class. But we’ll be utilizing joins in a variety of different ways moving forward into into some more complicated queries at any end. But I’m going to stop the video here. If you please feel free to reach out if you have questions.