MYSQL JOINS

MySQL JOINs: An Introduction

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

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 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 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 type of JOINs in MySQL. These are,

• Inner JOIN – It is simple JOIN to 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 left table and display the data from other table if the condition is satisfied. Otherwise, it will display NULL.
• Right JOIN – It is similar to Left JOIN, but works with respect to right table instead of left one.

Note:

MySQL JOINS can be applicable for SELECT, UPDATE and DELETE queries.
This MySQL tutorial was added on April 27, 2013.

http://phppot.com/php/mysql-joins-an-introduction/