Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Querying Relational Databases

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Inner Joins

5:44 with Andrew Chalkley and Kevin Nahm

Inner Joins are the most common type of Joins. Inner joins match records together where values are equal on both sides of the join statement.

INNER JOINs are the backbone of advanced SQL querying. There are literally thousands of books, articles and blog posts with examples and information about SQL joins.

Here are some of the links to help you explore INNER JOINs further:
W3Schools Inner Joins
Wikipedia: Inner Joins

In the video, we only showed two tables being joined together, but in reality, you can join in any number of tables.

If all tables are inner-joined together, the resulting data set will contain only rows that match on the specified relationships throughout all tables. The resulting Venn Diagram could get quite complex, and depends on many factors, including number of tables, and relationships used, but could look something like this:

ComplexVenn

Cheat Sheet

See all of the SQL used in Querying Relational Databases in the Querying Relational Databases Cheat Sheet.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up