MySQL Inner Join

The MySQL Inner Join is used to returns only those results from the tables that match the specified condition and hides other rows and columns. MySQL assumes it as a default Join, so it is optional to use the Inner Join keyword with the query.

We can understand it with the following visual representation where Inner Joins returns only the matching results from table1 and table2:

MySQL Inner Join Syntax:

The Inner Join keyword is used with the SELECT statement and must be written after the FROM clause. The following syntax explains it more clearly:

In this syntax, we first have to select the column list, then specify the table name that will be joined to the main table, appears in the Inner Join (table1, table2), and finally, provide the condition after the ON keyword. The Join condition returns the matching rows between the tables specifies in the Inner clause.

MySQL Inner Join Example

Let us first create two tables "students" and "technologies" that contains the following data:

Table: student

Table: technologies

To select records from both tables, execute the following query:

After successful execution of the query, it will give the following output:

MySQL Inner Join with Group By Clause

The Inner Join can also be used with the GROUP BY clause. The following statement returns student id, technology name, city, and institute name using the Inner Join clause with the GROUP BY clause.

The above statement will give the following output:

MySQL Inner Join with USING clause

Sometimes, the name of the columns is the same in both the tables. In that case, we can use a USING keyword to access the records. The following query explains it more clearly:

It will give the following output:

Inner Join with WHERE Clause

The WHERE clause enables you to return the filter result. The following example illustrates this clause with Inner Join:

This statement gives the below result:

MySQL Inner Join Multiple Tables

We have already created two tables named students and technologies. Let us create one more table and name it as a contact.

Execute the following statement to join the three table students, technologies, and contact:

After successful execution of the above query, it will give the following output:

MySQL Inner Join using Operators

MySQL allows many operators that can be used with Inner Join, such as greater than (>), less than (<), equal (=), not equal (=), etc. The following query returns the result whose income is in the range of 20000 to 80000:

This will give the following output: