Inner Join in MySQL with conditions by example

Before getting started with the inner join in MySQL with conditions examples, let’s know about the inner join keyword and its syntax.

What is inner join in SQL/MySQL?

Inner join is used when we want to collect the data or rows between two tables or from multiple tables in the database.

When the inner join is applied for two tables then it finds the exact matching values in both the tables. Once found, it returns all the matching data or rows from both tables. Matching values are connected by columns that are related to each other in terms of the primary key and foreign key.

Syntax:- (Two Tables)

select column name1,column name2,....... from table_name1 table_variable1 inner join table_name2 table_variable2 on table_variable1.matching column name = table_variable2.matching column name.

Syntax:- (Multiple tables)

select column name1,column name2,....... from table_name1 table_variable1 inner join table_name2 table_variable2 on table_variable1.matching column name = table_variable2.matching column name inner join table_name3 table_variable3 on table_variable2.matching column name = table_variable3.matching column name.

Let us take an example

Suppose we have two tables ‘category’ and ‘subcategory’ respectively.

id	category_name
1	Smartphone
2	Laptops

id	category	subcategory_name	price
1	1	Samsung	10000
2	1	Redmi	12000
3	1	Real Me	10000
4	1	Vivo	12000
5	2	Dell	30000
6	2	Lenovo Ideapad	30000
7	2	Asus	28000

If we want a table like this,

category_name	subcategory_name	price
Smartphone	Samsung	10000
Smartphone	Redmi	12000
Smartphone	Real Me	10000
Smartphone	Vivo	12000
Laptops	Dell	30000
Laptops	Lenovo Ideapad	30000
Laptops	Asus	28000

We have to write the query using inner join as follows-

SELECT category_name, subcategory_name, price FROM subcategory sub INNER JOIN category cat ON sub.category = cat.id;

Inner Join in MySQL with WHERE Clause

Inner join in MySQL with where clause is applied when we want to fetch the rows or data with some specific condition. For example, from the above tables, if we need all the smartphones under the price 10000 then we have to write the query as follows-

SELECT category_name, subcategory_name, price FROM subcategory sub INNER JOIN category cat ON sub.category = cat.id WHERE sub.price=10000;

category_name	subcategory_name	price
Smartphone	Samsung	10000
Smartphone	Real Me	10000

Conclusion:- I hope this tutorial will help you to understand the functionality of inner join in MySQL.

Also, want to know more about joins in SQL/MySQL

Also Read, GROUP_CONCAT() example in MySQL