MySQL Inner Join
In MySQL, the INNER JOIN clause is used to combine rows from two or more tables based on a related column between them. The INNER JOIN returns rows when there is at least one match in both tables.
If there are rows in the left table that do not have matches in the right table, those rows will not be included in the result set. This is one of the most commonly used types of joins.
In this article, we will look at what is "MySQL INNER JOIN" along with syntax and how it can be used with various keywords and clauses like GROUP BY, HAVING, WHERE, USING, and SQL Operators with the help of various examples.
MySQL INNER JOIN
The INNER JOIN keyword in MySQL selects only those tuples from both tables that satisfy the join condition. It creates the resultant set by joining all tuples from both tables where the value of the common column is the same.
Syntax:
SELECT <table1.column1>,<table1.column2>,....,<table2.column1>,.....
FROM table1 INNER JOIN table2
ON table1.condition_column = table2.condition_column
WHERE [condition];
GROUP BY <group-by-column-name>
HAVING [condition];
Parameters:
SELECT: Columns to retrieve from the resultant set.INNER JOIN: Joinstable1withtable2based on a matching condition.ON: Condition for how the tables are related (table1.condition_column = table2.condition_column).WHERE: Optional filter condition for rows after the join operation.GROUP BY: Optional clause to group the result set by specified columns.HAVING: Optional condition to filter grouped rows based on aggregate functions.
Examples of MySQL INNER JOIN
Let us consider the following schemas for the examples in the following article.
StudentDetails Table:
| id | sname | age | university |
|---|---|---|---|
| 1 | Girish | 24 | IIT Hyderabad |
| 2 | Aaditya | 24 | SRM University |
| 3 | Aashish | 23 | IIT Hyderabad |
| 4 | John | 25 | Mumbai University |
| 5 | Shruti | 24 | IIT Hyderabad |
| 6 | Leena | 25 | Mumbai University |
CourseDetails Table:
| cid | cname | ratings | price |
|---|---|---|---|
| 1 | Python Fundamentals | 4.6 | 2999 |
| 2 | Machine Learning | 4.3 | 1999 |
| 3 | DSA A-Z | 4.9 | 5999 |
| 4 | Competitive Programming | 4.7 | 4999 |
| 5 | Android Programming | 4.6 | 4999 |
EnrolledIn Table:
| sid | cid |
|---|---|
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 3 |
| 3 | 3 |
| 4 | 1 |
Example 1: MySQL INNER JOIN using Multiple Tables
We can also inner join to join more than two tables using the joining column condition.
Query:
Find the list of students of students enrolled in the "Python Fundamentals" course and output their details like student id, name,, and age.
SELECT S.sid, S.sname, S.age
FROM StudentDetails S
INNER JOIN EnrolledIn E ON S.sid = E.sid
INNER JOIN CourseDetails C ON C.cid = E.cid
WHERE C.cname = "Python Fundamentals";
Output:
Explanation: Our query needs the joining of StudentDetails, CourseDetails, and EnrolledIn Tables. The StudentDetails and EnrolledIn tables will be joined based on the sid column from both tables and then joined with the CourseDetails table based on the cid column. We also add a WHERE clause and check if the cname from the CourseDetails table is "Python Fundamentals".
Example 2: MySQL INNER JOIN with Group By & HAVING Clause
You can use GROUP BY and HAVING clauses along with MySQL Inner Join to write complex queries.
Query:
Find the list of courses where more than one student is enrolled and output the course name and count of students enrolled in that course.
SELECT C.cname, count(*)
FROM StudentDetails S
INNER JOIN EnrolledIn E ON S.sid = E.sid
INNER JOIN CourseDetails C ON C.cid = E.cid
GROUP BY C.cname
HAVING count(*) > 1;
Output:
Explanation: The output lists courses ("Python Fundamentals" and "DSA A-Z") with more than one student enrolled. It reflects the result of joining StudentDetails, EnrolledIn, and CourseDetails tables, grouping by course name (cname), and counting enrolled students (COUNT(*)).
Example 3: MySQL INNER JOIN with WHERE keyword
You can use WHERE keyword to further filter out the tuples that satisfy the particular condition specified in the WHERE clause.
Query:
Find the list of students from "IIT Hyderabad" university who have enrolled in at least one course and output their details like student id, name, and age.
SELECT distinct(S.sid), S.sname, S.age
FROM StudentDetails S
INNER JOIN EnrolledIn E ON S.sid = E.sid
WHERE S.university = "IIT Hyderabad";
Output:
Explanation: Our query needs an INNER JOIN of two tables, StudentDetails & EnrolledIn, with the condition that the student should belong to "IIT Hyderabad" university. The StudentDetails and EnrolledIn table joined on the sid column and its result with the CourseDetails table on the cid column with a WHERE condition for specifying the University name. Of the three students at "IIT Hyderabad" university, only "Girish" and "Aashish" have enrolled in at least one course.
Example 4: MySQL INNER JOIN with USING clause
If the name of the column on which two tables are to be joined is the same in both tables, then we can use the USING clause, which takes the joining column name as input.
Query:
Find the list of students from "IIT Hyderabad" university who have enrolled in at least one course and output their details like student id, name, and age.
SELECT distinct(S.sid), S.sname, S.age
FROM StudentDetails S
INNER JOIN EnrolledIn E USING(sid)
WHERE S.university = "IIT Hyderabad";
Output:
Explanation: Again an INNER JOIN of two tables, StudentDetails & EnrolledIn, with a condition that the student should belong to "IIT Hyderabad" university. This query asks to output the exact same thing asked in the example in point 4. Instead of using the ON keyword and mentioning the joining condition, we used the USING clause with the column name. Since the column name of the joining column, i.e. sid is the same, we will use the USING(sid) clause.
Example 5: MySQL INNER JOIN using Operators
We can use various different SQL Operators along with MySQL INNER JOIN, these operators can be arithmetic operators such as +, -, *, /, %, or they can be comparison operations such as equal (=), not equal (!=), greater than (>), less than (<) or they can even be logical operators like AND, OR, ANY, BETWEEN, EXISTS, etc.
Query:
Find the list of courses where at least one student is enrolled and the price of the course is greater than 4000 and output their course name, price, and ratings.
SELECT distinct(C.cname), C.price, C.ratings
FROM CourseDetails C
INNER JOIN EnrolledIn E ON C.cid = E.cid
WHERE C.price > 4000;
Output:
Explanation: Our query will consist of CourseDetails and EnrolledIn tables and will be joined based on the cid column from both tables. We add a WHERE clause and check if the price > 4000 to output courses with a price > 4000 and at least one student is enrolled. We have used the greater than (>) comparison operator in this query.
Conclusion
So, INNER JOIN in MySQL joins two or more tables and matches the rows based on the joining column condition. Only those rows appear in the resultant set where the condition is satisfied. In this article, we first looked at what MySQL INNER JOIN is and its syntax. Then, using various examples, we saw how MySQL INNER JOIN can be used for Multiple Tables, with GROUP BY and HAVING clause, WHERE keyword, USING clause and along with various operators such as arithmetic, comparison, logical, bitwise, etc.
