Introduction
A join lets you combine columns from two or more tables into a single result or say a SQL statement that retrieves data from two tables is called a join.
Types of join
- INNER JOIN
- OUTER JOIN
- SELF JOIN
I am describing here each of those one-by-one.
INNER JOIN
The inner join returns those rows with at least one match in both tables. If two columns in a join condition have the same name then you must qualify them with the table name so MySQL distinguishes between them.
This Example illustrates how to use an inner join clause in the PHP code.
Example
|
<?php
$con=mysql_connect("localhost","sharad","gupta");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("Employees", $con);
$qry="SELECT emp.id,emp.FirstName, dept.dept_name FROM emp INNER JOIN dept on emp.id = dept.dept_id";
$result=mysql_query($qry)ordie(mysql_error());
echo"<tableborder='1'>
<tr>
<th>EmpId</th>
<th>Firstname</th>
<th>DeptName</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['dept_name'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?> |
I show here the emp table data and dept table data separately.
Emp table data and dept table data
![join-in-php.jpg]()
Output
![inner-join-in-php.jpg]()
Note: As you see above, the emp and dept tables contain different columns, so in the above example I use a join clause between the two tables. I use "emp" and "dept" table and find the first name from the dept table, id and last name from the emp table where id in the emp table dept_id in the dept table are equal.
OUTER JOIN
Previously, I also described INNER JOIN. Now I am going to explain, how to use OUTER JOIN in PHP. Unlike an inner join, outer joins returns all of the rows from one of the tables involved in the join.
An Outer join contains the following types, I am explaining each one-by-one.
-
Left outer join
-
Right outer join
LEFT OUTER JOIN
Left outer join is the join based on the left table, and displays a null for the right table if the join condition is not true.
I show here emp table data and dept table data separately.
Emp table data and dept table data
![join1-in-php.jpg]()
Output
![left-outer-join-in-php.jpg]()
Note: Left outer join returns all the rows from the left table (emp), and at the right side table, those values which do not match, represents NULL.
RIGHT OUTER JOIN
A right outer join is pretty much the same thing as a left outer join, except that all the rows from the right table are displayed in the result table, regardless of whether or not they have matching values in the left table.
Example
|
<?php
$con=mysql_connect("localhost","sharad","gupta");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("Employees", $con);
$qry="SELECT emp.id, emp.FirstName,dept.dept_name from emp RIGHT OUTER JOIN dept on emp.id=dept.dept_id";
$result=mysql_query($qry)ordie(mysql_error());
echo"<tableborder='1'>
<tr>
<th>EmpId</th>
<th>Firstname</th>
<th>DeptName</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['dept_name'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?> |
I show here the emp table data and dept table data separately.
Emp table data and dept table data
![join3-in-php.jpg]()
Output
![right-outer-join-in-php.jpg]()
Note: A right outer join returns all the rows from the right table (dept), and at the left side table, those values which do not match, represents NULL.
SELF JOIN
A self join is another type of join and it is used to join a table to itself. Especially when the table has a foreign key which references its own primary key.
Example
|
<?php
$con=mysql_connect("localhost","sharad","gupta");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("Employees", $con);
$qry="select a.comp_name,b.comp_name,a.comp_city from company a, company b where a.comp_city=b.comp_city and a.comp_name<>b.comp_name";
$result=mysql_query($qry)ordie(mysql_error());
echo"<tableborder='1'>
<tr>
<th>CompanyName</th>
<th>CompanyCity</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['comp_name'] . "</td>";
echo "<td>" . $row['comp_city'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?> |
I show here company table data.
![join2-in-php.jpg]()
Output
![self-join-in-php.jpg]()
Note: In the above example "a" and "b" aliases for the table "company" and a.comp_city=b.comp_city excludes all pairs containing companies of various cities. And in the condition a.comp_city<>b.comp_city all pairs of the same company name so with it only those cities exist that are in the same city.