Cross Join in PHP

Introduction

A CROSS JOIN produces a result set in which each row from the first table is combined with each row from the second table. A Cross Join is also called a Cartesian Product. There are two types of Cross Joins; they are:

• Implicit Cross Join
• Explicit Cross Join

Explicit Cross Join

To use an "explicit cross join", write the "cross join" keywords after the first table name and before the second table name. Because of the way "cross join" works, you do not code the "ON" clause that includes a Join condition.

Syntax

The syntax of an explicit cross join is:

SELECT select_list FROM table1 CROSS JOIN  table2

In the following image the query "select fname, role from emp CROSS JOIN designation;" is an "explicit cross join" in which each row of the first table is combined with each row of the second table.

Implicitly Cross Join

In an "implicit cross join" you need not write the "cross join" keywords after the first table name and before the second table name, you can simply write the select statement and pass the table names with the names separated by commas in the from clause.

Syntax

The syntax of an implicit cross join is:

SELECT select_list FROM table1, table2

In the following image the query "select * from emp, designation" is an "implicit cross join" in which each row of the first table is combined with each row of the second table.

Example of Cross 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: Cross Join Explicitly</h2>";

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

echo "<table border='1'>

<tr>

<th>Role</th>

<th>Firstname</th>

</tr>";<br>

 

while($row = mysql_fetch_array($result))

  {

   echo "<tr>";

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

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

 

  echo "</tr>";

  }

  echo "</table>"; 

 

   print "<h2>MySQL: Cross Join Implicitly</h2>

$result = mysql_query("select fname,role from emp, designation ");

echo "<table border='1'>

<tr>

<th>Role</th>

<th>Firstname</th>

 

</tr>";

while($row = mysql_fetch_array($result))

  {

   echo "<tr>";

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

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

 

  echo "</tr>";

  }

  echo "</table>";

  mysql_close($con);

  ?>

Output

Summary

The "cross join" joins each row from the first table with each row from the second table. The resulting set is known as a Cartesian Product.