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 the 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 to 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

Database Table Structure

The structure and the data of these tables are shown below. The tbl_links table has a column named links_category. This column is used to map each link data with the corresponding tag by referring to the tag_id.

I have referred to 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

tbl_tags

Inner JOIN

Using inner join, I combine the two tables tbl_links and tbl_tags to read the data from both tables.

I wrote the 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 the inner join between the two tables the resultant data will be as,

Inner Join Output

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
• jQuery
• JavaScript
• WordPress
• MySQL
  • MySQL Basics
    • MySQL Data Types
    • MySQL Database Files
    • MySQL SHOW
    • MySQL CONCAT
    • MySQL OrderBy
    • MySQL Date Time
    • MySQL JOINs: An Introduction
    • MySQL Left Join
    • MySQL Right JOIN
    • MySQL vs MySQLi in PHP
  • phpMyAdmin
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js