Open In App

MySQL Inner Join

Last Updated : 19 Jan, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

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

StudentDetails Table

CourseDetails Table:

CourseDetails

CourseDetails Table

EnrolledIn Table:

EnrolledIn

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---Multiple-tables

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:

GROUP-BY

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

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

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:

INNER-JOIN---Operators

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.



Previous Article
Next Article

Similar Reads

INNER JOIN ON vs WHERE clause in MySQL
Have you ever wondered about the optimal use of INNER JOIN ON versus the WHERE clause in MySQL queries? In this article, we'll delve into the distinctions between these two approaches, elucidating their respective purposes, syntax, and best practices. By the end, you'll have a solid grasp of how to leverage these tools to enhance the efficiency and
4 min read
When should we use CROSS APPLY over INNER JOIN in MySQL
CROSS APPLY and INNER JOIN are used in MySQL to combine data from multiple tables. When dealing with row-wise operations or dynamic subqueries, CROSS APPLY is more efficient than INNER JOIN. CROSS APPLY allows for more detailed control on individual rows, while INNER JOIN operates on sets of data. It becomes very useful when we need to perform row-
4 min read
CROSS APPLY vs INNER JOIN in PL/SQL
PL/SQL Stands for procedural language extension to SQL. In a procedure, the role of the subprogram is to perform a particular task and it is a unit module of a program. It combined to form larger programs. A subprogram can be involved by another program which is called the calling program. PL/SQL provides a block structure of executable unit code.
6 min read
How Inner Join works in LINQ to SQL
LINQ (Language Integrated Query) in C# provides a powerful way to query data from various data sources, including databases. In LINQ to SQL, which is specifically designed for working with relational databases, an inner join is a common operation used to combine records from two tables based on a specified condition. This article explores the conce
3 min read
Difference Between Anti-Join and Semi-Join
In the context of SQL, Anti-join, and semi-join are two essential operations in relational databases used for querying and manipulating data. These operations focus on comparing data from two related tables, but they serve distinct purposes. In this article let us discuss these two operations in detail along with some examples. What is SQL Join?Joi
4 min read
MySQL RIGHT JOIN
In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, joins enable the merging of multiple tables based on the common columns. In this article, we are going to explore MySQL RIGHT JOINS which is a type of outer join in MySQL. Introduction to MySQL RIGH
7 min read
MySQL DELETE JOIN
MySQL is an open-source, user-friendly, powerful, and popular choice, relational database management system. When maintaining and modifying data, tables usually interact in a complex way. MySQL's DELETE JOIN function is one of its most powerful functions. MySQL DELETE JOIN is explored in detail in this article, which also offers examples to help vi
3 min read
MySQL UPDATE JOIN
A widely used open-source relational database management system that allows you to efficiently store, organize, and retrieve data. Developed by Oracle, My SQL is widely used for building and managing databases that handle interactive websites and applications. We'll discuss the syntax, and demonstrate how this dynamic duo can efficiently modify dat
6 min read
MySQL CROSS JOIN
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. Among these operations, MySQL also provides the CROSS JOIN statement to combine
3 min read
MySQL SELF JOIN
Joins are very important for effective data retrieval and analysis. The 'JOIN' clause is used to combine data from two or more tables using the common column between them. In MySql, there are many types of joins like inner join, outer join, left join, right join, full join, and self join. In this article, we will discuss the concept of MySQL SELF J
4 min read