MySQL INNER JOIN

In this tutorial, we will study the MySQL INNER JOIN. First of all, let us start with what a join is. A join is used to combine two or more tables using a commonly related column between them. Suppose if you have an ‘OrderDetails’ table and a ‘Products’ table then you can create a join using the ‘product_id’ column that would be present in both the tables.

There are different types of joins in MySQL. They are –

• Inner Join
• Left (Outer) Join
• Right (Outer) Join
• Full Outer Join

In this article, we will focus on the Inner join and how to use the INNER JOIN keyword to make an inner join in MySQL.

What is an Inner Join?

The INNER JOIN keyword is used to select only those records that have matching values in both the tables. In other words, if you have two tables, then inner join selects only the common records present in both tables. It works like the intersect operation you would have seen in set theory in mathematics.

Let us demonstrate the INNER JOIN using a diagram.

As you can glean from the diagram, only the common records between the two tables are selected. The rest are ignored in an inner join.

Syntax of MySQL INNER JOIN

SELECT expression FROM table1 [t1] 
INNER JOIN table2 [t2] 
ON table1.column_name = table2.column_name;Code language: SQL (Structured Query Language) (sql)

Here ‘t1’ and ‘t2’ are optional aliases that you can have for the table names. We will see more of it in the examples. The columns mentioned after the ON keyword are the related common columns using which the inner join is to be performed.

Examples of MySQL INNER JOIN

Consider the below Students table and Marks table.

The Students table stores the details of the student in every row. The Marks table stores every student’s marks in English, Maths and Science out of 100.

Simple Example of the MySQL INNER JOIN

Let us now make an inner join on the Students table and Marks table such that we see a student’s ID, first name, last name, and their marks in English. 

The first step is to find the commonly related columns between the two tables. In this case, they are ID from the Students table and StudentID from the Marks table. Let us now write a query for the inner join using the SELECT statement.

SELECT s.ID, s.FirstName, s.LastName, m.English FROM Students s 
INNER JOIN Marks m 
ON s.ID=m.StudentID;Code language: SQL (Structured Query Language) (sql)

We use ‘s’ as a shorthand notation to refer to the Students table and ‘m’ for the Marks table. We get the output as follows –

Using Expressions in a MySQL INNER JOIN Query

Building on the previous query, let’s make an inner join on the Students table and Marks table such that we see a student’s ID, first name, last name and their percentage. To calculate the percentage of every student, we add their marks in the three subjects, divide it by 300 and then multiply that with 100. The query is –

SELECT s.ID, s.FirstName, s.LastName, ((m.English+m.Maths+m.Science)/300)*100 AS Percentage FROM Students s 
INNER JOIN Marks m 
ON s.ID=m.StudentID;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

MySQL INNER JOIN With the ORDER BY clause

Again, let us build on the previous example. What if you want to know the rankings of students by percentage? Let’s make an inner join on the Students table and Marks table such that we see a student’s ID, first name, last name and their percentage.

This time, let us order the result in descending order of Percentage value. For this, we will use the ORDER BY clause.

SELECT s.ID, s.FirstName, s.LastName, ((m.English+m.Maths+m.Science)/300)*100 AS Percentage FROM Students s 
INNER JOIN Marks m 
ON s.ID=m.StudentID ORDER BY Percentage DESC;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

MySQL INNER JOIN With the HAVING clause

Now, let us display the ID, first name, last name and percentage of the students who have more than 85%. Since we will have to filter the query through the alias Percentage, we will use the HAVING clause instead of the WHERE clause.

SELECT s.ID, s.FirstName, s.LastName, ((m.English+m.Maths+m.Science)/300)*100 AS Percentage FROM Students s 
INNER JOIN Marks m 
ON s.ID=m.StudentID HAVING Percentage>85;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

MySQL INNER JOIN Ignores Uncommon Records

In the above tables, every student ID had details in both the tables. Now let us look if one table has a given student ID while the other doesn’t. As you’d expect, INNER JOIN will ignore that record. Let’s see this.

It is Manvi Khanna’s first day in school today. Her phone number is 7845998865 and she is from Nagpur. Using the INSERT INTO statement, let us write a query to insert her record into the Students table. We will use the SELECT statement to display the newly updated Students table.

INSERT INTO Students VALUES(7, 'Manvi', 'Khanna', 1, 7845998865, 'Nagpur', 0); 
SELECT * FROM Students; Code language: SQL (Structured Query Language) (sql)

Now let us perform a simple inner join on the two tables and see our output.

SELECT s.ID, s.FirstName, s.LastName, m.English FROM Students s 
INNER JOIN Marks m 
ON s.ID=m.StudentID;Code language: SQL (Structured Query Language) (sql)

As you can see, Manvi Khanna’s record is ignored by the INNER JOIN keyword as her details are not yet present in the Marks table.

Conclusion

INNER JOIN is an extremely important operation when it comes to MySQL. Joining two tables and displaying that data provides useful insights into the data. I would highly recommend you to practice some examples on INNER JOIN.