MySQL JOINs are used to read data by connecting two or more related tables. While joining MySQL tables, we need to specify the condition for the JOIN query with the use of ON or USING clause.
The join conditions are written based on the common column name, data or indexes of the tables.
In this tutorial, we are going to see the types of MySQL JOINs and a basic introduction for each type. I have added an example for the Inner JOIN to get the joined result from the tables tbl_links and tbl_tags.
Types of JOINs
|Inner JOIN||This type of JOIN is the most common type. It is used to get the intersected set of data from the tables based on the JOIN condition.|
|Outer JOIN||It is also known as Full Outer JOIN. Query with this type of JOIN is used to all the records from the tables. The resultant table will state NULL wherever the JOIN condition is not matched.|
|Left JOIN||Left JOIN is used to fetch all the records from the left table. In the resultant structure, it contains data for the right side table columns if match found or NULL otherwise.|
|Right JOIN||Right JOIN is used to fetch all the records from the right side table. In the resultant structure, it contains data for the left side table columns if match found or NULL otherwise.|
Database Table Structure
The structure and the data of these tables are shown below. The tbl_links table has a column named as links_category. This column is used to map each link data with the corresponding tag by referring the tag_id.
I have referred this column as a foreign key index of the tbl_links table. I have used these common column key indexes in the below examples of MySQL JOINs.
Using inner join, I combine the two table tbl_links and tbl_tags to read the data from both the table.
I wrote join condition to check the mapping between the tables with the use of the columns tbl_links.links_category and the tbl_tag.tag_id having common data. The query checks if the join condition is satisfied and gives intersected data as shown in the Venn diagram.
The inner join query is,
$query = SELECT a.links_name, b.tag_name FROM tbl_links a INNER JOIN tbl_tags b ON a.links_category = b.tag_id
This can also be written by using implicit JOIN,
$query = SELECT a.links_name, b.tag_name FROM tbl_links a, tbl_tags b WHERE a.links_category = b.tag_id
After applying inner join between the two tables the resultant data will be as,
Inner Join Output