How to Use Joins in PHP

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

Output



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

Output



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