MySQL JOINs: An Introduction

This article is started with MySQL simple JOIN concept with a PHP script. JOINs in database queries are used to retrieve data from more than one table using a single query. On executing the join query, this will return a table in a virtual manner which is not present in database actually. For example, let there is two table in database namely tbl_links, tbl_categories, and their structure is like as below format.

mysql_join

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              |
+-----------------+----------+

The tbl_links table has a links_category field as it’s foreign key where the primary key tag_id of tbl_tags will be stored here. Now we can create a table of data which is spread over these two tables, by comparing these two keys. For that, we are going to use MySQL JOINS as shown below.

$query = SELECT a.links_name, b.tag_name FROM tbl_links a, tbl_tags b WHERE a.links_category = b.tag_id

Before executing this query using PHP, we need to connect the database, about what we have discussed already in Access MySQL from PHP. This query will result in a table to be displayed to the browser as follows.

tbl_links
+---------------+-------------------+
| links_name    | tag_name          |
+---------------+-------------------+
| google.com    | Search Engine     |
| phppot.com    | PHP               |
| facebook.com  | Social Networking |
+-----------------+-----------------+

Types of JOINs

There are several types of JOINs in MySQL. These are,

  • Inner JOIN – It is simple JOIN to get common data between two tables as shown in this article.
  • Outer JOIN – It is also called as Full Outer JOIN, which will retrieve all the rows from both tables and represent NULL for the column index if the condition of the JOIN is not satisfied.
  • Left JOIN – Retrieves all the rows from the left table and display the data from another table if the condition is satisfied. Otherwise, it will display NULL.
  • Right JOIN – It is similar to Left JOIN, but works with respect to the right table instead of left one.

Note:

MySQL JOINS can be applicable for SELECT, UPDATE and DELETE queries.

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

↑ Back to Top