MySQL JOINs: An Introduction

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. 

join-more

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

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 google.com 3
2 phppot.com 1
3 facebook.com 2

tbl_tags

tag_id tag_name
1 PHP
2 Social Networking
3 Search Engine
4  Java

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.

inner-join

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
google.com Search Engine
phppot.com PHP
facebook.com Social Networking

This PHP code tutorial was published on April 27, 2013.

↑ Back to Top