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:
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.
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:
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:
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.
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:
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:
SELECT student_id, inst_name, city, technology
FROM students
INNER JOIN technologies
USING (student_id);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:
SELECT tech_id, inst_name, city, technology
FROM students
INNER JOIN technologies
USING (student_id) WHERE technology = "Java"; 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:
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 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:
SELECT emp_id, designation, income, qualification
FROM employee
INNER JOIN customer
WHERE income>20000 and income<80000; This will give the following output:
Leave a Reply