Join Tables With the Using Keyword in PHP

Introduction

 

I have also described in my previous article how to work with "inner" and "outer" joins. You can follow this link:

 

How to Use Joins in PHP

 

to read about "inner" and "outer" joins in PHP. Now in this article I will tell you how to join tables with the "Using" keyword. The "Using" keyword is used in place of the "On" keyword. So the question is, if we have the "On" keyword then why do we need the "Using" keyword? The answer of this question is, suppose you have more than one table, and you want to join them. Now we have two tables ("emp", "designation"), See following image.

 

The emp table consists of four columns (id, fname, lname and salary) and the designation table consists of two columns (id and role). Now you can see that both tables have a column named "id". When you code an "equi join", it's common for columns that are being compared to have the same name. For joins like these, you can simplify the query with the "Using" keyword. The following query image shows that the "Using" keyword can be used instead of the "On" keyword. In the following example we have joined two tables using their id's. And on the basis of the id column name, the emp role will be returned from the designation table.  

 

 

In the following query image, we have joined two tables using their ids. And on the basis of the id column name, his/her role is returned from the designation table.

 

The summary of the preceding explanation is that you can use the "Using" keyword instead of the "On" keyword when you want to join more than one table and the tables have the same column name, and you want to compare these  table's data using the same columns name. You can use the "Using" keyword.

Syntax

SELECT select_list from table1
 [{LEFT/RIGHT} [OURT JOIN] tanbe2
USING (JOIN_Column1, JOIN_Column2, JOIN_Column3....................)
[{LEFT/RIGHT} [OURT JOIN]] table2
USING (JOIN_Column1, JOIN_Column2, JOIN_Column3....................)]............................

In some cases, you may want multiple columns. To do that with the using clause, you can code multiple column names within parentheses, separating the columns name with commas.  It acts the same as "and" operators. Since the "Using" clause is more terse than the "On" clause, it can make your code easier to read and maintain.

 

Example of "Using" keyword in PHP

<?php
$con=mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("mysql", $con);
print "<h2>MySQL: Data of Emp table</h2>";
$result = mysql_query("select * from emp");
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Salary</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
   echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['fname'] . "</td>";
  echo "<td>" . $row['lname'] . "</td>";
  echo "<td>" . $row['salary'] . "</td>";
  echo "</tr>";
  }
  echo "</table>";
  print "<h2>MySQL: Data of Designation table</h2>";
$result = mysql_query("select * from designation");
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Role</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
   echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['role'] . "</td>";
  echo "</tr>";
  }
  echo "</table>";
 print "<h2>MySQL: Join with USING keyword</h2>";
$result = mysql_query("select fname,salary,role from emp CROSS JOIN designation USING(id) ORDER BY salary");
echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Role</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
   echo "<tr>";
  echo "<td>" . $row['fname'] . "</td>";
   echo "<td>" . $row['role'] . "</td>";
  echo "</tr>";
  }
  echo "</table>";
  mysql_close($con);
  ?>

Output