Joins are some of the most powerful tools you have in relational databases. Joins allow you to JOIN one or more tables together, a very common practice in SQL.
This lesson discusses the default Join, which is also the most common Join in SQL: the INNER JOIN.
What is Inner Join in SQL?
The SQL INNER JOIN command will select all records that have matching values in the two chosen tables.
SQL Inner Join Example
The best way to understand SQL INNER JOIN is to look at some concrete examples. Below is the step-by-step process explaining how it works.
Note: This example uses the MySQL Sakila demo database. If you haven't already, follow the steps to install the Sakila DB, then head back here.
1. Select your Tables to Join
The first step is to choose the tables you want to join! In this example, you'll JOIN the actor, film_actor, and film tables.
2. Select the Join Columns
You join tables using their columns. To join tables, there must be corresponding columns that have corresponding values.
For example, in your tables, both the actor and film_actor tables contain the column actor_id. Therefore, for every row in the actor table, you can associate the corresponding row in the film_actor table that has the same actor_id.
You can JOIN the film table with the film_actor table because they both contain the field film_id.
3. Optional: Add a Conditional Statement
As always, you can filter the results with a condition instead of receiving more information than necessary. One way to do this is using the WHERE statement. For instance, select all the films that the actor "PENELOPE GUINESS" is in.
4. Run the Query
And that's it! Write out the query and run it.
SELECT actor.first_name, actor.last_name, film.title, film.release_year -- fields to select
FROM actor --first table to select from
INNER JOIN film_actor -- first table to join
ON actor.actor_id = film_actor.actor_id -- the field on which to join the two tables
INNER JOIN film -- second table to join
ON film_actor.film_id = film.film_id -- the field on which to join the two tables
WHERE actor.first_name = "PENELOPE" AND actor.last_name = "GUINESS"; -- boolean condition to filter results
Here it is in MySQL Workbench:
SQL Inner Join Example - Explained
There's a lot going on here, so let's see if we can unpack it. (The upcoming video should also help clear it up.)
So, you want to select the first name and last name from the actor table, and the film title and release year from the film table. The only way to do this is to JOIN through the film_actor table.
This is because the actor table has no data about films whatsoever, and the film table has no data about actors whatsoever. Fortunately, you have this handy film_actor table that links actor_ids with film_ids.
Using this lookup table, you can now associate the actor with actor_id = 1 to the film with film_id = 1. As well as the actor with actor_id = 1 to the film with film_id = 23, and so on.
Using this lookup table, you can now link the actor table with the film table by linking the actor_id from the actor table to the actor_id in the film_actor table, and then you link the film_id from the film_actor table to the film_id in the film table.
Take a look at that query again.
It is making a bit more sense now? Maybe? Hopefully? :)
Inner Join Tips
INNER JOINis the default join statement. Therefore, you actually don't need to write the wordINNER. So this query can be re-written as follows:
- Aliases make queries shorter
Additionally, you can further shorten the query by not typing the table names over and over and over. Instead, you can create aliases (or nicknames) for each table and then refer to the tables by those aliases. See below:
And here it is again without all the cluttery comments:
Makes perfect sense, right?! Don't worry if this is a bit confusing at the moment; that is totally normal. It will make sense soon enough! Just keep on practicing and pushing through.
Summary: The Inner Join SQL Statement
The SQL INNER JOIN command will select all records that have matching values in the two chosen tables.
SQL Inner Join Tips:
INNER JOINis the default join command, so the wordINNERdoes not need to be written- Tables must share a column with similar data types and values (Id's, for example)
- A condition can be added after the
INNER JOINcommand to filter the resulting data - Aliases can be used instead of writing the full table name each time
How to Write an Inner Join in SQL
- Choose your tables
- Select the join columns
- Optional: add a conditional statement
- Run the query
SQL Inner Join Syntax
Here's the syntax for the SQL INNER JOIN command, where you can substitute the variables in < > with your values.
SELECT <YOUR_COLUMNS>
FROM <YOUR_TABLE_1>
INNER JOIN <YOUR_TABLE_2>
ON <YOUR_TABLE_1.COLUMN> = <YOUR_TABLE_2.COLUMN>
Now that that's done, it's time to learn LEFT and RIGHT joins ;)