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.

If we want a table like this,

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;

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