(upbeat music)
- Joins are used
to create a single result set of records
from multiple tables.
Because relational database management systems
use normalization and split the data into multiple tables.
Joins are a required element in queries
that need to extract data from these different tables.
In essence, a join is the mechanism you use
to tell SQL Server what field to use from one table
to locate and select records
from a related table.
Remembering the concept of foreign keys,
they are a key in one table that equates to a primary key
in another table.
This key establishes the relationship between the tables.
Joins typically use a logical operator
such as equals or the not equal when comparing the columns,
but they can also use other operators
such as like, in, and between.
Joins come in various forms.
The default is known as an Inner Join.
This type of join returns all the matching records
in the included tables.
And Inner Join can be specified in the from
or the where clause,
although it is most common to use it in the from clause.
Outer Joins are another type of join,
and they come in three flavors known as left, right,
and Full Outer Joins.
We also have full joins, cross joins, and self joins.
To get an understanding of how the joins function
or how we can visualize these joins,
let's go ahead and take a look at a Venn diagram.
So we've got two overlapping circles,
which indicate the different types of joins
and how they function.
We've already mentioned that the inner join
will return all matching records from both tables.
So again, if we go back and think about our set theory,
if we were to join both sets in return the results
for all of the values that exist in both,
that would be the inner join.
It would not return records that don't exist in both tables.
Your left outer join will return all the records
from the left table in the join clause,
regardless of matching records in the right table.
Fields from the right table with no match
will display null values.
A right outer join will return all the records
in the right table in the join clause,
regardless of matching records in the left table.
In this instance,
fields from the left table with no match
will display no values.
A full outer join, as you might expect by now,
will return all the rows in both tables.
When rows don't have a match in one of the tables,
the field will display a null value.
The next join we take a look at is the cross join.
A Cross join returns all rows from the left table,
which is then combined with all the rows
from the right table.
This type of join is also known as a Cartesian product,
and it's not often used due to the large amount of results
that get returned.
You can also join a table to itself
in what is known as a self join.
You would do so when you want it to create a result set
joining records in the table
with some other records from the same table.
An example of that might be where we have an employee table,
where we have managers listed because they are employees.
And we would like to take a look at a result set
that returns all of the employees
and indicating who their managers are.
So because managers exist in the employee table,
we would run a self join on that.
Joins only act on, at most two tables at a time.
But having said that
you can include multiple join statements in a from clause
to include more tables in the join.
Each join clause must include two tables
that have a relation field such as the foreign key.
Now our first query will be selecting a product name
from our product table,
but then we're going to combine that with comments
and a product review ID from the product review table.
And the product review ID will be the matching keys,
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,
product review ID
and comments from the product review 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 and 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 inner join the product review table
on the product table by using the product ID.
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 Product Review ID,
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 table.
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 Product review table.
So we run that query.
We can see that indeed Product review
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 were querying,
the product table and a product review table,
but the inner join returns only the subset of those values
or of those records
where we have matching product ID versus product ID.
So again, as we run that query,
we only return four records
because there are only four products
that actually have a review.
All right. 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.
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 product review table,
but again, the key point to note about this left outer join
is we may not have matching records
in the product review 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 product review table,
we'll see no values.
So let's run this query.
And sure enough,
we have no product ID and no comment
for actually 500 of these rows,
and so we see no 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 no value.
Let's look at our next example.
We're going to use the left outer join again,
but this time we're going to put the product review 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 it might be a little surprised
at the result of this one
because when we run it, we only see four rows.
You might've 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 product review
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.
Product review is the left table, it only has four rows,
therefore we only get four rows return.
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 product review
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 product review table
on the left.
Let's think about that for one second.
Product review table on the left,
and we return four records,
Product review 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 product review 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 product review 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,
product review ID and comments.
We've got our product table listed,
we're doing a full outer join on the product review 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
'cause they're both on the outer side of that query.
(upbeat music)