MySQL Join Made Easy For Beginners: An Introduction

MySQL-JOINs

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

TypesDescription
Inner JOINThis 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 JOINIt 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 JOINLeft 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 JOINRight 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_idlinks_namelinks_category
1quickmysupport.com2
2facebook.com1
3bing.com3

tbl_tags

tag_idtag_name
1Social Networking
2PHP
3Search 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.

MySQL-JOINs

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_nametag_name
quickmysupport.comPHP
facebook.com Social Networking
bing.comSearch Engine

By Rodney

I’m Rodney D Clary, a web developer. If you want to start a project and do a quick launch, I am available for freelance work. info@quickmysupport.com

Leave a Reply

Your email address will not be published. Required fields are marked *