After learning join theory, this video shows how to apply that knowledge in actual queries that join multiple tables to return results.
- [Instructor] In this lesson we're going to see an example of using joins in a query. We're using SQL Server Management Studio, and the exercise file for this one is the 04_04_Using_JOINS_in_a_Query.sql. So we're going to demonstrate the use of inner joins, left and right outer joins, and then we're also going to demonstrate the use of a full join. To give you an idea of how each of these works we've set up some different samples in here, so again we're using our AdventureWorks2014 database, let's make sure we select it up here.
Now our first query will be selecting the Product Name from our Product table, but then we're going to combine that with Comments and a ProductReviewID from the ProductReview table, and the ProductReviewID will be the matching key, so that is what we will use as the relationship between the two tables. This is where we really start to make use of table aliases because we're bringing back data from tables sometimes with the schema and the table name becoming rather long, we don't want to have to start typing Production.ProductReview in front of every column name that we want to return from that table, we're using table aliases in this example.
So the first query that we have is we're going to select Name from the Product table, ProductReviewID and Comments from the ProductReview table. Notice that we set up our SELECT statement and then in our FROM statement we say we want to select from the Production.Product table. Because that's listed first when we create this inner join, that becomes the first table that SQL will take a look for specific data from. In the INNER JOIN portion we specify the Production.ProductReview table as the table we want to join.
Note that putting the INNER keyword in here is optional, because if we just type JOIN, an inner join is the default that SQL Server will use if we don't specify any other type of join. It's always nice to be very explicit in what you're doing, so I have a tendency to use INNER JOIN even though it is the default, that way when I'm reading my code later, I know precisely what I was attempting to do. After the JOIN statement, we then have to make the comparison or tell SQL Server which fields it needs to match on to find the related data in each one of these tables.
That's where the ON keyword comes into play, and that's where our predicate here helps to bring back those related records. So we're saying, I want you to join the ProductReview table, on the Product table, but using the ProductID, so that's what this ON p.ProductID equals pr.ProductID means. So let's run this query and see the results. You'll notice that we have the Name returned, we have a ProductReviewID, and then we have the Comments that were entered for that product review.
Now that's only four rows and it seems like really not a whole lot of data coming back from the tables so we might want to verify that the data is correct. So, let's do that by first of all taking a look at the Product table, let's query the Product table and see how many records we have. 504 rows, so we might look at that and think, has our query worked successfully, have we returned all of the records that are appropriate? Well, let's add another little verification step in here by selecting everything that we have available in the ProductReview table.
So if we run that query, we can see that, indeed, ProductReview only contains four actual comments, so four records that we have against all of those 504 rows. So that gives you a little bit of insight into how that inner join works. We have two sets that we're querying, the Product table, and a ProductReview table. But the inner join returns only the subset of those values or of those records, where we have matching ProductID versus ProductID.
So again as we run that query, we only return four records, because there are only four products that actually have a review. Alright, so that's the inner join, let's take a look at the use of an outer join. So we're going to look at a left outer join first, and what a left outer join does, as we indicated in the overview of understanding joins, the left join will return all rows in one table, regardless if there's a match in the other table. Now in this case, we're going to return all the records from the left table, and that becomes the Product table because we're listing that Product table first.
So Product table becomes the LEFT portion of our OUTER JOIN. Now we're also going to return records from the ProductReview table, but again the key point to note about this left outer join, is, we may not have matching records in the ProductReview table, and again we know, let's take a look down here at our results from that previous inner join query, there are only four product reviews. We have 504 products listed in the actual database, so that means that we can't possibly have 504 matching records, but what the left outer join does for us, is it returns everything from that left table, Product in this case, and everything from the right table that doesn't have a match, which is our ProductReview table, will see null values, so let's run this query.
And sure enough, we have no ProductID and no Comment for actually 500 of these rows, and so we see null values in those placements, and if you were to scroll far enough through here you would find, eventually one of those four records that actually did have a review, and I think we just passed one, so we can see that where there is a match, we do get the values, but where there isn't a match in the right table, we just simply get a null value. Let's look at our next example. We're going to use a left outer join again, but this time, we're going to put the ProductReview table as the left table in the join.
Again, thinking about how this works, we return all rows from the left table, and any row from the right table that doesn't have a match will be given a null value. I think you might be a little surprised at the result of this one because when we run it, we only see four rows. You might have been thinking we should see the 504, or the 505, whatever that count was, because the left outer join on the Product table returned 500 and some odd rows, why wouldn't the left join on the ProductReview also return 500 and some odd rows? Again the reason being is that we have to look at how that left outer join works.
It returns all rows in the left table, ProductReview is the left table, it only has four rows, therefore we only get four rows returned. Let's switch for a moment and look at a right outer join. We're going to see some similarities, especially because we're using two tables that are identical so the left outer join and the right outer join, we're using the same tables in each one of these joins, but we're going to change the side that the table sits on, for the query. So in this case we're going to do a right outer join, and because the Product table is listed first it becomes the right table.
So you may be able to guess how the results will look from this one but let's run it, and we see, oh interesting, we only returned four rows, why did we only return four rows? Because there's only matching records in the right table, in other words the ProductReview has actually been the one that we've queried so, this right outer join that we have selected here, actually worked the same as putting the ProductReview table on the left. Let's think about that for one second.
ProductReview table on the left, and we return four records. ProductReview table on the right, we return four records. One more example of a right outer join, just to help solidify our understanding. Here we're listing the ProductReview table first and the Product table second, we're executing a RIGHT OUTER JOIN, now we come back to what we saw initially with that first left outer join, where we had the Product table on the left, and the ProductReview table on the right.
So, just be cognizant of what you're doing in terms of left and right outer joins, and where your table placements are within your join statements. Finally let's look at a full outer join. So this says we'll retain the non-matching rows, regardless of whether there is a match in the other table. Now the results are going to look similar to that left outer join in this instance, and that's just because of the way the data exists in these tables so, we're still doing the same Product Name, ProductReviewID and Comments, we've got our Product table listed, we're doing a FULL OUTER JOIN on the ProductReview table, let's run this query and see what we end up with.
And sure enough, 505 rows, but again it looks similar to that left outer join. So, we're getting all of the records, whether they're matching or not, from both tables because they're both on the outer side of that query. So this is just a quick demo of some of the different types of joins available in SQL Server, and how you can make use of them in your querying, to bring back a specific set of records depending on your needs.
- Writing SELECT queries
- Querying multiple tables
- Filtering text and duplicates
- Sorting and grouping query results
- Using the built-in functions of SQL Server
- Writing subqueries
- Using common table expressions
- Programming with Transact-SQL
- Interpreting query performance data