Join Review

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at our last and final join outer joins. Outer Joins in particular have three different variations for left and right. But before we get into a new joint, let’s take a look at the material that we’ve covered so far. So last time, we covered a lot of cross joins and inner joins, which are both table operators, that we can utilize to have queries that pull from more than one table. Initially, we covered only tables that we’re pulling from just a single table. And so our FROM clause wasn’t very complicated. But then we learned we can use things like cross join an inner join, to pull from, you know, two, three, or even more different, different tables and join those all into one single table as a result, SQL Server in particular, supports four different table operators join, apply, pivot and pivot. For this class, we are only focusing on join in particular, but and join is the only standard operator anyways, apply pivot and Unpivot. You may not see in other SQL languages like my SQL and Postgres.

So we’ve covered CROSS JOIN, inner join. So far OUTER JOIN is the one that we’ll actually hit today in this video. But joins in general right there, the operation, the purpose of the operation is to combine two tables together into one. So the left hand side of the operation must be a table, the right hand side of the operation must be a table. And the result of the operation is also a table. And so that’s why we can actually chain these joins together to produce one single table as an output. But each of the joins that we’ve that we’ve covered CROSS JOIN inner join, differ in their processing phases as well. So we’ve we’ve focused a lot on this logical processing phase, right the the fact that our query is listed in a different order than it’s actually processed. So joins have their own processing phases as well. So we’ve already hit cross an inner join, but let’s review those cross join is the most simplistic out of the two. So CROSS JOIN just produces the Cartesian product between the two tables. So all the combinations of rows from Table A, with all the combinations of row of rows from Table B, and those are paired together, and that’s the table as an output. But INNER JOIN is going to build off of that. So INNER JOIN is also the Cartesian product.

So it’s a cross join, but with the filter, right, so the inner join has the table a inner join table B on and then some predicate. And we learned last time that we can actually filter on more than one thing, right, just like what we can do with the where clause, we can have an AND, and OR, or, or even more as part of that. So let’s kind of review in general what these joints look like as well. So remember our table A that has Jim Kim analysis, we have ID and name as the two columns. And then Table B, we’ve got ID and food as the two columns, pickles fish and ice cream. And when I cross join those, we get a much larger table, where we have pickles, fish and ice cream paired with Jim pickles, fish and ice cream joined with Kim, and pickles, fish and ice cream along with Alice. So all the combinations of rows from Table A with all the combinations of rows from Table B. But then we introduced a inner join, where an inner join has a predicate associated with it. So on something right, and so here, I’m going to enter join on the ID column. So give me all of the join table A with Table B, but only join records that have a matching ID. And so the only two records in this case that have matching IDs are Kim and Alice. So Kim with an ID of two matches up with pickles, which also has an ID of two, and then Alice which has an ID of three pairs perfectly well with fish associated with ID three as well. But those are our two joints that we’ve covered so far. And the next video will actually dive into a whole new join the OUTER JOIN