Natural Join in PHP

Introduction

When you want to "join" two tables based on a common column then you can use an "equi join" and in this process you specify on the "using" keyword to specify the columns, of which tables are to be joined. So the "Natural" keyword or "Natural join" provide a way to join tables without specifying the column name. In other words when you code a "natural join", you do not specify the column that is used to join the two tables. The database automatically joins the tables using all the columns in the two tables that have the same name. This type of join can only work correctly on certain types of designed databases.

For example

Suppose you have more than one table, and you want to join them. Now we have two tables ("emp","designation"). See the following image. And you want to join the following tables without specifying their column name(s); you can simply use a "natural join" to do that.

In the following image, the query "SELECT id, fname , salary , role from emp NATURAL JOIN designation" joins the emp and designation tables using the id column (but without specifying the id column explicitly). But if the tables had another column in common, this query would attempt to join theses tables on both columns and would yield unexpected results.



Then if you want to use "NATURAL Join" with complex quires then you can use the "USING" or "ON" clause to explicitly specifying the column name. You can write within a single select statement "natural join" and "ON" or "USING" clause. I have written a query in which three tables will be joined into a single result set.

Data of Table JOINING



Query of Natural Join + Using clause in a single select statement

In the following image, the query "select id ,fname, salary, role, hire_date from emp Natural JOIN designation LEFT JOIN joining using (id)" uses a "natural" join for the first statement and a "USING" clause for the second join.



Example of Natural Join in PHP

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("mysql", $con);

 

print "<h2>MySQL: Join with Natural keyword</h2>";

$result = mysql_query("select id,fname,salary,role from emp NATURAL JOIN designation");

echo "<table border='1'>

<tr>

<th>Empid</th>

<th>Firstname</th>

<th>Salary</th>

<th>Role</th>

</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row['id'] . "</td>";

echo "<td>" . $row['fname'] . "</td>";

echo "<td>" . $row['salary'] . "</td>";

echo "<td>" . $row['role'] . "</td>";

echo "</tr>";

}

echo "</table>";

 

print "<h2>MySQL: Join with Natural keyword (other Example)</h2>";

$result = mysql_query("select id,fname,salary,role,hire_date from emp NATURAL JOIN designation LEFT JOIN joining USING(id)");

echo "<table border='1'>

<tr>

<th>Empid</th>

<th>Firstname</th>

<th>Salary</th>

<th>Role</th>

<th>JoiningDate</th>

</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row['id'] . "</td>";

echo "<td>" . $row['fname'] . "</td>";

echo "<td>" . $row['salary'] . "</td>";

echo "<td>" . $row['role'] . "</td>";

echo "<td>" . $row['hire_date'] . "</td>";

echo "</tr>";

}

echo "</table>";

mysql_close($con);

?>

Output



Summary

The NATURAL JOIN code is shorter than the code for joins that uses ON or USING and also provides the functionality to join tables on all columns in the two tables that have the same name.