MySQL Joins

In the MySQL Select statement chapter, we learned how to use the select statement of MySQL to fetch data from the single table. In relational database systems, different tables are related to each other by foreign or related keys.

In many scenarios, we need to fetch data from multiple tables in order to present some information or perform some statistics by using the single query. This can be done by using joins.

MySQL comes up with different types of joins that allow us fetching data from more than one tables. In this chapter, we will show you how to use joins with examples. You can also find links to different join types in MySQL at the end of this tutorial.

The Join clause is used to fetch data from more than one table by using common keys in different tables.

General syntax of join clause

The general syntax to use join clause is:

Select * from table_name1

Join table_name2 ON table_name1.common_field = table_name2.common_field

Join examples

In our examples to explain joins, we will use three tables that are created in our test database. These tables are related by the common keys and will be used in join queries. The tables are:

1. tbl_employee
2. tbl_customers_orders
3. tbl_products

The tbl_employee table is used to store employees information. Another table, tbl_products stores product information while tbl_customer_orders is used to store order information like customer id, product id, employee id etc.

A simple join example

In the following example, the scenario is we want to retrieve data of orders and which employees (operators) taken those orders with employees information. The tbl_employee and tbl_customer_orders are related by emp_id key, that we will use in the join query as follows:

The join query will be:

MySQL inner join example

Following is an inner join in MySQL example where we will retrieve information from tbl_customer_orders table for only those employees who took orders. The employees who did not take any order will not be fetched as their emp_id does not exist in tbl_customer_orders table. You can see query and output by clicking the link below:

The  inner join query:

To learn more about the inner join go to its chapter here: The Inner join

A left join example

Following is a left join example. All rows from one table, towards select statement (left), will be retrieved whereas only matched records will be fetched from the right table, after the join clause. Click on the link below to see query and its output:

The left join query:

To learn more about the left join go to its own chapter here: MySQL left join

A right join Example

Following example will retrieve all records from the right table which is after the join clause and only matched rows from the left table i.e. the one after select statement.

See the graphic of all tables and join

The right join query:

As you can see in above graphic, all rows are retrieved from tbl_customers_orders.

MySQL joins Types

Following are Join types in MySQL:

1. MySQL Inner join
2. MySQL Right join
3. MySQL left join