Introduction to Joins

Video Transcription

Welcome back everyone. In this video series, we’re going to be talking about joins with databases. So up to this point, we’ve focused on single table queries. But now we can start working on bringing in data from multiple, multiple tables and more or multiple data sources. joins, we’re going to be talking about overall in this set of videos, cross joins, Inner Joins, and pretty much variations of those. So the primary two types of joins that we’ll be focusing on our cross joins and inner joins. But we’ll also talk about outer joins as well here and a few. First, before we get into looking at multi table queries, let’s review what we’ve done so far. So first off, real big point, real big point to drive home here is the processing order. So remember, SQL is kind of different are kind of weird in comparison to the programming languages that you’re used to. So most programming languages are going to go from execute from top down, even when you’re working inside of individual functions. But with SQL, the the order of which that you actually program your query, and is going to be different from the order that that query is processed. Let’s remember that we don’t start off with the SELECT clause, we actually start with the from, so we have to know what our data sources first. And then we can filter those rows from those tables. Using the where clause, we can optionally group those rows up together based off of a certain number of columns or column expressions. And then we can actually also filter those groups. But remember, the having clause can only be used with the group by cuz having filters group by group, and remember, were filters row by row. So where can only be paired with from and having can only be paired with group by, then our select clause gets executed. So now we pick out which columns we want in our results. And then once our columns are selected, we can then determine distinct or not.

So by default, remember, we return all rows. But if we want to return only unique records, so no duplicate rows, we can use the distinct qualifier. And then after that we can actually order or sort. Remember, by default, our order by is in ascending order. So whichever columns you want to order by, if you don’t specify direction, it sorts by ascending. But you can also use descending order as well. After order by we can also use top if we want to reduce the number of rows further that are actually returned. So let’s say give me the top five rows from the results of this query. Now remember, top usually, you always want to pair with order by because otherwise, it’s non deterministic. So the results will be different every single time or not guaranteed to be the same every time you run it. But the answer NC standard for this, of course, is the offset fetch, which is also ran alongside the order by so orders first and then if offset fetches president that gets executed along with that statement. So in this video series, we’re going to be taking a look a little bit more into table operators primarily because so far, we’ve only utilized single table queries up to this point, in our FROM clause, we can use a combination of different table operators in order to perform a little bit more advanced query. So selecting data for from more than one table, for example. And so when we actually pull information from our tables, we we perform all sorts of other operations on them using like the where clause selecting order, by group by all those sorts of things. But within the from clause, SQL Server itself supports only four different operations there. And the ones that we’ll be focusing on today are going to be joints, particular. So how do we combine or bring together more than one table, there’s also apply pivot and Unpivot.

I’m listing those three there, primarily because they are opera operations that can be used inside SQL Server. I’m not going to cover those in this class. But if you’re interested in learning more about them, I’ve linked in the slide deck here I’ve linked the sequel documentation for those. So please feel free to go and read more about those. But join here is the only standard operator from from the anti standard so Apply pivot Unpivot those operations are not necessarily going to be guaranteed to be present in other database management systems like MySQL or Postgres. But nonetheless, let’s talk about joins. As I’ve already kind of alluded to joins are used to join more than one table. So the joint operation itself takes on the left hand side, a, on the left hand side, one table and on the right hand side the other table and then produces a single table as a result. So there’s three types of joins that we’re going to work with cross joins, Inner Joins, and outer joins. And all three of these are going to differ in the order of which they actually operate. But in the following videos, we’re going to take a look at each of these joins by themselves.