The inner join of SQL

• The SQL inner join only returns the matched records from joined tables.
• The Inner Join in SQL combines data of two or more tables by common keys.
• The inner join or simple join is the type of join that is most commonly used.

An example of inner join

To make it clearer, consider the following scenario to understand the inner join.

Generally, you store data in different tables in a relational database. These tables are then related by the foreign keys or some common columns to keep the relationship between data. For example, in a departmental store’s point of sales system, employees data may be stored in one table called tbl_employee.

Another table will store the products information in the store, that may be tbl_products. As the consumers come to shop in that store and complete the orders, this order should be related to an operator/employee.

The purpose of that information may lead to reports that may be required on the daily, Weekly, Monthly or Yearly basis. The reports may include products that are sold, how may orders are taken by a specific operator etc.

As such, the data is stored in the different tables of the database, in order to get a combined report from different tables, the SQL joins play a very important role. SQL has the different type of Joins and the inner join is one of those.

General syntax of SQL inner join

The general syntax of inner join:

Select * from table_name1

Inner Join table_name2 ON table_name1.common_field = table_name2.common_field

Examples of using the inner join clause

For our examples of the inner join, we will use the scenario as explained in the above part. A departmental stores database tables:

1. tbl_employee
2. tbl_customers_orders
3. tbl_products

The requirement is to retrieve only those employee’s data who have records in the tbl_customers_orders table. That means only those employees who have taken orders should be returned.

The SQL inner join query:

See the graphic of all tables and inner join

As you can see in above graphic, first data of two tables are shown which is followed by the query with SQL inner join clause.

As shown in the graphic, it only returned the matched rows. That is, only those records are retrieved for employees whom ids are in the tbl_customers_orders table by using the inner join in SQL.