What Is an SQL INNER JOIN?

How do you combine data from different tables in one query? An SQL feature called JOIN is the most common operator used to create complex queries. Learn the basics in this article.

SQL allows us to select data from more than one table. In fact, the whole idea of using relational databases is selecting data from related tables. And we can use SQL’s JOIN operator to do this.

There are many types of JOINs in SQL. In this article, we’ll focus on INNER JOIN, which is the default JOIN command (i.e. the one you get if you use the JOIN keyword by itself).

The best way to review and practice all types of JOINs in SQL is our interactive SQL JOINs course. It contains over 90 hands-on practice exercises and covers all the different types of SQL JOINs.

When Do You Need JOIN?

Why would we need to join data from several tables? The simple answer is that sometimes one table doesn’t contain all the data you need.

Imagine that the movie table stores information in the columns id, title, production_year, and director_id.

Another table, director, contains information about directors: their id, name, and birth_year.

Notice that each movie has only one column related to the director: director_id. The same ID number values in the table director (in the director_id column) and in the table movie (in the id column).

We’d like to display each movie’s data together with the details of its director. The result is a new table with data from two tables: movie and director. The tables are combined (or joined) based on the directors’ ID numbers. In other words, movies with a specific director_id are combined with the information of the director with this ID – e.g. the movie Psycho with director_id=1 is matched by the record where id=1 (Alfred Hitchcock) in the director table. Look at the result set:

An illustrated version of a similar example is presented in the article An Illustrated Guide to the SQL INNER JOIN.

How to Use JOIN in an SQL Query

Now, let’s analyze the query we just used. Here it is again:

SELECT *  
FROM movie  
JOIN director  
ON movie.director_id=director.id;

JOIN is a shorter form of the INNER JOIN clause; you can use them interchangeably.

After you SELECT your columns, you put the FROM keyword and the name of the first table (in this example, movie). Then you put JOIN (or INNER JOIN), followed by the name of the second table (here, director). Next is the ON keyword and the join condition (i.e. how to match records from both tables). In this case, the join condition matches the values in the column director_id in the table movie with the id column in the table director.

In this way, records in the table movie are matched to the correct records in director. Now we know that the movie Sweet and Lowdown was directed by the director Woody Allen, who was born in 1935. This movie has director_id=2; there is a matching value to the corresponding column (id) in the table director.

The animation below shows how using a JOIN operator works and which records are returned:

A JOIN operator works logically, in two steps. The first step returns a Cartesian product, i.e. each row in the first table is combined with each row in the second table. In the next step, only the pairs of records which meet the condition in the ON clause are returned – in this example, only rows where director_id in movie equals id in director.

Note that the values used in the join condition don’t have to be numbers. You can also use text values and other more complex conditions. You can find more information in the article SQL INNER JOIN Explained in Simple Words.

What If Records Don’t Match?

What if there are records in the first table that cannot be matched to records in the second table, or vice versa? How does the INNER JOIN work in this case? Let’s look at an example. Here is the movie table again:

And here is director:

Here is the same query:

SELECT title, production_year, director_id,
       id, name, birth_year   
FROM movie  
JOIN director  
ON movie.director_id=director.id;

The result table contains only records that are a matching pair:

If you look at the table movie, the column director_id is NULL for the movie The new house. And in the table director, the director Martin Scorsese (id=4) is not related to any row in the table movie. Therefore, these rows are not displayed in the result set.

The rule for INNER JOIN (JOIN) is that only rows with matching in both tables are returned. In the other types of JOIN operators this behavior is different. Extend this topic reading the article SQL JOIN Types Explained.

Filtering Records in the Result Set

You can also filter records returned by JOIN. For example, we can list only those movies and their directors where the director was born after 1940. Here’s the query:

SELECT title, production_year, director_id,
       id, name, birth_year   
FROM movie  
JOIN director  
ON movie.director_id=director.id
WHERE director.birth_year>1940;

It returns the result:

Once again, we have rows from the table movie combined with rows from the table director. Initially, the join condition is the same (ON director.id=movie.director_id).

However, in the next step, records with a value less than or equal to 1940 in the column birth_year are removed. Thus, you don’t find Woody Allen in the result set because he was born in 1935 (before/less than 1940). Alfred Hitchcock is not here either; he was born in 1899. We did this thanks to WHERE director.birth_year>1940.

Want to Learn More About SQL JOINs?

If you're looking for a handy reference for SQL JOINs, check out our SQL JOINs Cheat Sheet which provides a detailed overview of the different types of JOINs.

Don't forget to try our SQL JOINs course, which contains a detailed overview of different types of JOIN (including lots of practice exercises!).

I also suggest you look at How to Learn SQL JOINs, and SQL JOIN Types Explained. Happy learning!