MySQL Inner Join

Have you encountered a query requiring you to somehow join or get the data spread across two or more tables? Are you wondering how to achieve the same by writing a MySQL query? Do you want to learn about MySQL INNER JOIN? If your answer to any of these questions is “YES”, you have arrived at the correct place. Follow along with the below article and champion writing MySQL INNER JOIN queries.

A JOIN in SQL can be seen as a Cross Product/Cartesian Product of two tables and then doing a selection process by filtering out rows based on some condition. During any JOIN operation, two tuples from different tables are matched only if the provided joining condition is satisfied. An INNER JOIN is a type of JOIN where only those tuples appear in the resultant set that satisfies the joining column condition.

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];

In the above syntax, table1 is our first table, table2 is our second table, and condition_column is a column used to join both tables using the INNER JOIN keyword. Also, WHERE, GROUP BY,, and HAVING clauses are optional and may be used depending on the SQL query.

NOTE: It’s a good practice and convenient to use alias table names with short names to avoid writing lengthy table names repeatedly in case of complex INNER JOIN queries.

Examples of MySQL INNER JOIN

Let us consider the following schemas for the examples in the following article.

StudentDetails Table:

StudentDetails Table

CourseDetails Table:

CourseDetails Table

EnrolledIn Table:

EnrolledIn Table

Example 1: MySQL INNER JOIN using Multiple Tables

We can also inner join to join more than two tables using the joining column condition.

Syntax:

SELECT <list-of-column-names>

FROM table1

INNER JOIN table2 ON <condition-column>

INNER JOIN table3 ON <condition-column>

……

INNER JOIN tableN ON <condition-column>;

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:

INNER JOIN using Multiple Tables

Explanation:

Our query needs 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.

Syntax:

SELECT <table1.column1>,<table1.column2>,….,<table2.column1>,…..

FROM table1 INNER JOIN table2

ON table1.condition_column = table2.condition_column

GROUP BY <group-by-column-name>

HAVING [condition];

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:

MySQL INNER JOIN with GROUP BY and HAVING clause

Explanation:

Our query needs join of StudentDetails, CourseDetails, and EnrolledIn Table. 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 then apply GROUP BY on cname column and HAVING clauses to check if the count is more than one.

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.

Syntax:

SELECT <list-of-column-names>

FROM table1 INNER JOIN table2

ON table1.condition_column = table2.condition_column

WHERE [condition];

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:

INNER JOIN with WHERE clause

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.

Syntax:

SELECT <list-of-column-names>

FROM table1 INNER JOIN table 2

USING(<joining-column-name>);

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:

INNER JOIN – USING clause

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 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.

Syntax:

SELECT <table1.column1>,<table1.column2>,….,<table2.column1>,…..

FROM table1 INNER JOIN table2

ON table1.condition_column = table2.condition_column

WHERE <condition-column> <operator> <value>;

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:

MySQL INNER JOIN using operators

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.