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 the tables. Matching values are connected by columns that are related to each other in terms of the primary key and foreign key.

When the inner join is applied for multiple tables then it finds exact matching values in all the tables. Once found, it returns all the matching data or rows from all the 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.

idcategory_name
1Smartphone
2Laptops
category
idcategorysubcategory_nameprice
11Samsung10000
21Redmi12000
31Real Me10000
41Vivo12000
52Dell30000
62Lenovo Ideapad30000
72Asus28000
subcategory

If we want a table like this,

category_namesubcategory_nameprice
SmartphoneSamsung10000
SmartphoneRedmi12000
SmartphoneReal Me10000
SmartphoneVivo12000
LaptopsDell30000
LaptopsLenovo Ideapad30000
LaptopsAsus28000

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_namesubcategory_nameprice
SmartphoneSamsung10000
SmartphoneReal Me10000
Output

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


4 thoughts on “Inner Join in MySQL with conditions by example

  1. I’m amazed, I must say. Rarely do I encounter a blog that’s both equally educative and engaging, and without a
    doubt, you’ve hit the nail on the head. The problem
    is something which too few people are speaking intelligently about.
    I’m very happy that I found this during my hunt for something relating to this.

  2. Hi there are using WordPress for your blog platform? I’m
    new to the blog world but I’m trying to get started and set up my own. Do you need any html coding expertise to make your own blog?

    Any help would be greatly appreciated!

Leave a Reply

Your email address will not be published. Required fields are marked *

Theme of 96 THEME.