MySQL Join Made Easy For Beginners: An Introduction

MySQL Join 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 MySQL Join

Types	Description
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.

tbl_links

links_id	links_name	links_category
1	quickmysupport.com	2
2	facebook.com	1
3	bing.com	3

tbl_tags

tag_id	tag_name
1	Social Networking
2	PHP
3	Search Engine

Inner JOIN

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

links_name	tag_name
quickmysupport.com	PHP
facebook.com	Social Networking
bing.com	Search Engine