What is inner join of MySQL?

The inner join is a type of join in MySQL which is mostly used. In that join type, only those rows are returned that are matched in both tables. The two tables are joined by the common keys. In this chapter, we will cover MySQL inner join with examples.

In relational database management systems, we store data in different tables. These tables can be related to each other by using primary and foreign or other commons keys in order to maintain the integrity of data.

For example, in a departmental store DBMS, we may have a table of employees to keep their information. Another table can be customer_orders, that is used to store the orders placed by customers. The orders can be taken by only those employees whom data exist in the employees’ table.

To maintain integrity, you can relate both tables so that DBMS will not allow to enter data in customer_orders table from any person whom data does not exist in the employee table.

Similarly, you may need periodic reports of employees who have taken orders in order to judge their performance. For the reports, you need data to be retrieved from multiple tables. This is where you can use joins to use a single query to fetch data from multiple tables and inner join is one of the types available in MySQL.

Inner join syntax

The syntax to use inner join is:

Select * from table_name1

Inner Join table_name2 ON table_name1.common_field = table_name2.common_field

MySQL inner join example

The following example shows how to use inner join in MySQL. For the example, we have three tables as given below.

1-      tbl_employee

2-      tbl_customers_orders

3-      tbl_products

The tbl_employee is used to store employees data while tbl_products stores the products information. The third table, tbl_customer_order is used to store order information that also include product_id and employee_id of those employees who are taking those orders.

Now we will fetch information of employees whom IDs also exist in tbl_customer_order table. The query and output can be seen by clicking the link:

The inner join query:

You can see, the query only returned matched records from both tables.

Also see MySQL Join