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:
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:
SELECT columns FROM table1 INNER JOIN table2 ON condition1 INNER JOIN table3 ON condition2 ...;
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.
Let us first create two tables "students" and "technologies" that contains the following data:
To select records from both tables, execute the following query:
SELECT students.stud_fname, students.stud_lname, students.city, technologies.technology FROM students INNER JOIN technologies ON students.student_id = technologies.tech_id;
After successful execution of the query, it will give the following output:
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.
SELECT students.student_id, technologies.inst_name, students.city, technologies.technology FROM students INNER JOIN technologies ON students.student_id = technologies.tech_id GROUP BY inst_name;
The above statement will give the following output:
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:
SELECT student_id, inst_name, city, technology FROM students INNER JOIN technologies USING (student_id);
It will give the following output:
The WHERE clause enables you to return the filter result. The following example illustrates this clause with Inner Join:
SELECT tech_id, inst_name, city, technology FROM students INNER JOIN technologies USING (student_id) WHERE technology = "Java";
This statement gives the below result:
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:
SELECT student_id, inst_name, city, technology, cellphone FROM students INNER JOIN technologies USING (student_id) INNER JOIN contact ORDER BY student_id;
After successful execution of the above query, it will give the following output:
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:
SELECT emp_id, designation, income, qualification FROM employee INNER JOIN customer WHERE income>20000 and income<80000;
This will give the following output: