Hay friends, how are you? and what it is going on? are you learning more and more? if yes then get ready for a new tutorial. Today we’ll explain a powerful MySQL query keyword called INNER JOIN. We will also learn it’s the usage with different examples. Here is what we are going to learn:

Table of contents

• Introduction to INNER JOIN
• INNER JOIN Syntax and Explanation
• INNER JOIN Example Query
• Explanation of query
• Other Types of JOINs
• Conclusion

Introduction to INNER JOIN

Basically in SQL, JOINs are used to combines more than one table. If you have multiple tables and want to grab data from that multiple tables than you have to use JOIN. By using the “MySQL INNER JOIN”, the SQL query will select all rows from two or tables as long as there is a match between the columns. One thing more, this SQL INNER JOIN works the same as JOIN works. In fact, JOIN combines rows from two or more tables. Let’s take a look at its syntax.

INNER JOIN Syntax and Explanation

Below I am giving syntax of INNER JOIN query so you understand the basics of this Keyword.

SELECT * 
FROM table1 INNER JOIN table2 
ON table1.column_name = table2.column_name; 

SELECT * 
FROM table1
JOIN table2 
ON table1.column_name = table2.column_name; 

As you can see in the above two syntaxes, we used INNER JOIN and a simple JOIN. Both will work. We then, in the first syntax, select all columns, then we used the “INNER JOIN” keyword to join two tables. After that, we used an “ON” keyword to tell the query that match return those columns data where table 1 volume will match to table2 column value. At the time, it is a little bit difficult to understand but the below query will clear the road.

INNER JOIN Example Query

Suppose we have two tables. One is “products” and the second one is “dealers”. Below are listed both tables.

The above table can be generated with below commands/queries. This will generate a table called “products” with these columns “products_id, product_name, dealer_id” with assigning PRIMARY KEY to “products_id” columns. Also, this query it’ll put 04 rows of data related to the “products” table.

CREATE TABLE `products`(
	`products_id` INT NOT NULL AUTO_INCREMENT,
	`product_name` VARCHAR(255)NOT NULL,
	`dealer_id` INT(11)NOT NULL,
	PRIMARY KEY(`products_id`)
)ENGINE = INNODB;

INSERT INTO `products`(
	`products_id`,
	`product_name`,
	`dealer_id`
)
VALUES
	(NULL,'Dell Optiplex 760','1'),
	(NULL, 'Infinix Hot Pro', '2'),
(NULL,'Dell Optiplx 755','1'),
(NULL,'Oriont Mobile X2','5');

The queries for the above table are listed below. You can use any MySQL editor to get tables from these query strings. I recommend PhpMyAdmin, a free web-based tool to manage MySQL databases. This query will create a table “dealers” with dealers_id, dealer_name, dealer_city, and dealer_contact columns. Also, this query will insert data of two rows related to the “dealers” table. These data will help us to examine the query result that we will generate at the end.

CREATE TABLE `test`.`dealers`(
	`dealers_id` INT NOT NULL AUTO_INCREMENT,
	`dealer_name` VARCHAR(255)NOT NULL,
	`dealer_city` VARCHAR(255)NOT NULL,
	`dealer_contact` CHAR(30)NOT NULL,
	PRIMARY KEY(`dealers_id`)
)ENGINE = INNODB;

INSERT INTO `dealers`(
	`dealers_id`,
	`dealer_name`,
	`dealer_city`,
	`dealer_contact`
)
VALUES
	(NULL,'2BNET','Lalamusa','0000-00000000'),
	(NULL,'E-Store','Islamabad','1111-11111111');

Using the above table’s data we need another table that will contain data of those tables where the dealer is same. Look at below query:

SELECT dealers.dealer_name,dealers.dealer_city,dealers.dealer_contact,
products.product_name
FROM dealers 
INNER JOIN products 
ON products.dealer_id = dealers.dealers_id;

With above query it should return following result.

Explanation of Query

In the above C-5 SQL query, we first select dealer_name, dealer_city, and dealer_contact columns from dealers table along with product_name from the products table.

Then We used “FROM” to select the “dealers “table and used INNER JOIN on the “products” table. After that with “ON” keyword we told the query to match dealer_id from products table and dealers_id from dealers table.

In short words, the query will return all the products where dealers are matched from the “dealers” table. I am giving one more query. This will not return specific columns but will return columns of both tables. Look at this query:

SELECT * 
FROM dealers
JOIN products 
ON products.dealer_id = dealers.dealers_id;

Other Types of JOINs

There are 04 types of JOINs in MySQL. These can be used as per query needed. Here, below are the some other types of the JOINs used in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables (We learned it here)
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table (See this post)
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table (See this post)
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Conclusion – Bye Bye

In the end I requested you that don’t rely only on this post to keep searching more and more for improving your knowledge and make your base strong. Everyone is a student. No one is a master. So keep learning and spread knowledge. Thanks for reading this post.

Before leaving this page I suggest reading these tutorials if you need more information about MySQL queries.

• Useful MySQL Math Functions
  Hi all, how is it going on? Hoping you are fine. Today we will learn some useful MySQL math functions that will help you a lot in your future projects. As you all know that MySQL is a powerful database […]
• Using MySQL INNER JOIN Keyword
  Hay friends, how are you? and what it is going on? are you learning more and more? if yes then get ready for a new tutorial. Today we’ll explain a powerful MySQL query keyword called INNER JOIN. We will also […]
• Fetching Data from Multiple Tables Using JOIN
  Dear visitors, I hope you will be fine and hope everything is going well. Today I’ll talk about MySql “Join”. What is join? how does it work? What is the syntax? and Why should we use it? We will discuss […]
• Get the SUM of Values from another Table
  Our today’s topic is a little bit tricky. Sometimes you need to Get the SUM of Values from another Table to get the required data. This can also be done via PHP. But doing this with PHP is not a […]
• Finding Values in Comma Separated String Using MySQL
  In most cases, finding values in a comma-separated string becomes necessary. You can do the same job with PHP or any other language but doing it directly in MySql is easy and fast. In this article, you’ll learn about “Finding […]

If you are interested in PHP language that has a perfect relation with MySQL then I recommend to read following posts.

• 
  Get Part of a String from a Long String in PHP
  A few days ago, I faced a problem when I need to get part of a string in a long text. I feel felt very difficult to grab my required data. But then I tried to find the way and I finally did it. So now I am sharing it with you but first, let’s […]
• 
  Add or Subtract Days from Current Date in PHP
  In this tutorial, we’ll learn how to add or subtract days from the current date or date and time in PHP. To solve problems related to dates and times, PHP provides strtotime(), date_create(),date_add(), and date_sub() functions and a DateTime class. You can use strtotime() or DateTime class on your choice. We are going to use […]
• 
  Better Way to Manage HTML Tags With PHP
  When building web pages or web applications, at some point you have to combine PHP and HTML to manage HTML tags with PHP. This seems complicated because PHP and HTML are two separate languages, but this is not the case. There is a better way to manage HTML tags with PHP. As you know PHP […]
• 
  Calculate Days Months and Years in PHP
  Friends today I am going to tell you about another interesting function of PHP that will calculate the difference of Days, Months and Years between two dates. You can find days, months and years between two dates. You can use this function where you need to count days for billing or for the expiration of the user […]
• 
  Get the MAC and IP address of the Windows System with PHP?
  Friends today I am going to teach you to get MAC address and IP address of the Windows system with PHP. The trick is very simple don’t be afraid. You just need to grab MAC and IP of the system with PHP and then you will use it for any purpose where it required. Hope […]