Preview
Video Player
00:00
00:00
Inner Joins
5:44Inner Joins are the most common type of Joins. Inner joins match records together where values are equal on both sides of the join statement.
We're going to take
a look at the inner join.
0:00
The inner join is the most
common kind of SQL join.
0:02
Remember that a join is the way a query
instructs the database to combine
0:06
the data from two tables.
0:11
When writing queries for reports or
applications, most of the time the data
0:13
we need is in more than one table, so
we need to join multiple tables together.
0:18
Let's take a look at how that works.
0:23
Open up the SQL Playground
with this video.
0:26
We have here a database for
an auto sales company.
0:29
We have several tables, Car,
Customer, ForeignMake, Location,
0:32
Make, Model, Sale, and SalesRep.
0:37
Right now,
we're just gonna focus on Make and Model.
0:41
Eventually, we want to answer
the question, what are all Chevy models?
0:44
So let's start out with a select query for
all makes.
0:50
We do that with the SELECT keyword and
then a FROM clause.
0:57
Until now you've seen lots of examples
that specify one table in the FROM
1:03
section like this, but what if we wanted
to see the car's model information
1:08
as well as the make information
in the same result set?
1:12
We have to instruct
the database on how to do that.
1:16
We can add the Model table as a second
table after the FROM keyword, and
1:19
in order to do that we have
to use the keywords INNER JOIN
1:24
followed by the table
name you want to join.
1:28
But we also have to tell the database
which columns to join on.
1:33
For this we have to use
the ON keyword followed by
1:37
the two columns we want
to perform the join on,
1:44
in our case it's make.MakeID and
model.MakeID.
1:50
A few things to note here.
1:57
First, we use an equal sign to
tell the database that it's
2:00
matching the values from
from the makeID column from
2:05
the make table to the makeID
foreign key in the model table.
2:10
What this does is for every value
of the makeID in the Model table,
2:15
it will look for the corresponding primary
key column makeID in the Make table.
2:20
Second, when specifying the columns for
the join,
2:26
we had to be really specific and
include the table names, as well.
2:31
That's because the columns
are named the same in both tables.
2:35
And without the table
names being specified,
2:41
the database wouldn't have been
able to parse the query correctly.
2:43
Now let's clean it up a bit.
2:48
Let's say we really want to see just
the make name and the model name.
2:49
That's better, one last trick here,
2:59
just like aliasing columns we can
also alias tables in the FROM clause.
3:02
Doing this is a good habit to
get into as it makes writing and
3:11
maintaining complex queries much easier.
3:16
So let's alias make to mk and model as md,
3:25
and then we can replace make with mk,
3:31
model with md, and
add it to our SELECT columns.
3:36
When when this query now we
get the same results, but
3:43
with a little bit cleaner code,
which is a good practice.
3:46
Now let's have a look for
just our Chevy models.
3:49
We can add a WHERE criteria
3:53
after the from and inner joins,
3:57
WHERE MakeName = "Chevy".
4:02
You don't need to include
the table name or
4:11
alias the columns that aren't
in both tables like MakeName.
4:14
If we run this, we get the same results.
4:18
However, being more explicit
in a query is always good.
4:23
To recap, when we do an inner join,
the database is going to return
4:28
the rows where the values match on
both sides of the table relationship.
4:33
In this example if we join the ID field,
the values 2, 4, and 7 would not get
4:38
returned from Table 2 because there
is no matching values in Table 1.
4:45
If you remember back to
our video on set theory,
4:50
a Venn diagram of the inner
join would look like this.
4:54
To recap,
to write an inner join statement, first we
4:58
select the columns from both tables
you want to return in the result set.
5:01
Then the FROM keyword with the first table
name, then the keywords INNER JOIN and
5:06
the second table name.
5:11
Then the ON keyword with
the equality criteria.
5:13
This is generally between
the primary key of one table and
5:19
the foreign key of another.
5:22
The example we've seen here just joins
two tables together, but queries aren't
5:23
limited to just using two tables,
you can join as many tables as you want.
5:28
How many you include in your query just
depends on what you need in your results.
5:34
In fact, you're required to
join on more than two tables
5:38
when you have a many-to-many relationship.
5:41