In This Tutorial
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
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:
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:
select * from tbl_employee TE join tbl_customers_orders TC ON TE.emp_id=TC.emp_id
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:
select * from tbl_employee TE inner join tbl_customers_orders TC ON TE.emp_id=TC.emp_id
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:
select * from tbl_employee TE left join tbl_customers_orders TC ON TE.emp_id=TC.emp_id
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.
The right join query:
select * from tbl_employee TE right join tbl_customers_orders TC ON TE.emp_id=TC.emp_id
As you can see in above graphic, all rows are retrieved from tbl_customers_orders.
MySQL joins Types
Following are Join types in MySQL: