Cross Join
The principal join is a CROSS JOIN, which matches every row in one table against every row in another. This is an unfiltered join. The other join types start with this concept and filter the result set.
Another way to describe an CROSS JOIN is as a Cartesian Product, which is a combination of two sets where each row in one is matched to each row in another. The formula multiplies the sets, which means every ordered pair of (x,y) is unique. Each element x is an element (∈) in set A, and (∧) each element y is an element (∈) in set B. The ordered result set of (x,y) is not equal to the ordered result set of (y,x).
The CROSS JOIN may contain an intersection of rows because the ordered pair of (x,y) is the ordered pair of rows from the table on the left, or A, and they are matched against the rows from the table on the right, or B. Those elements (rows) in A that share an attribute (column) value with an attribute (column) value in an element (or row) from B become the intersection of a join. The intersection is also known as the INNER JOIN of the tables.
SQL CROSS JOIN Example ↓
Rule(s):
- A cross join doesn’t have a join statement.
Nested Loop Cartesian Product ↓
This is a PHP programming example of how nested loops can produce a Cartesian Product from two sets. A Cartesian Product is synonymous with a CROSS JOIN.
You should notice that a CROSS JOIN uses a nested loop to match every return of the inner loop with a return of the outer loop. There is no attempt to match values to narrow the return set to an intersection between sets. An intersection in a database join is the process of linking a row of data in one table with a row of data in another table based on the values in one or more columns found in each table.
<?php // Declare two arrays, two with data and one for the combined data. $english = array(array("ENGLISH_ID"=>"1","ENGLISH_TEXT"=>"One") ,array("ENGLISH_ID"=>"2","ENGLISH_TEXT"=>"Two") ,array("ENGLISH_ID"=>"3","ENGLISH_TEXT"=>"Three") ,array("ENGLISH_ID"=>"4","ENGLISH_TEXT"=>"Four") ,array("ENGLISH_ID"=>"5","ENGLISH_TEXT"=>"Five") ,array("ENGLISH_ID"=>"6","ENGLISH_TEXT"=>"Six")); $french = array(array("FRENCH_ID"=>"1","FRENCH_TEXT"=>"Un") ,array("FRENCH_ID"=>"3","FRENCH_TEXT"=>"Trois") ,array("FRENCH_ID"=>"4","FRENCH_TEXT"=>"Quatre") ,array("FRENCH_ID"=>"5","FRENCH_TEXT"=>"Cinque") ,array("FRENCH_ID"=>"6","FRENCH_TEXT"=>"Six") ,array("FRENCH_ID"=>"7","FRENCH_TEXT"=>"Sept") ,array("FRENCH_ID"=>"8","FRENCH_TEXT"=>"Huit")); $result_set = array(); // The larger array is always the outer loop and inner loop the smaller array. for ($i = 0;$i < count($french);$i++) for ($j = 0;$j < count($english);$j++) // This selects the elements from the cross joined set, like a SELECT clause in SQL. $result_set[] = "[".$english[$j]["ENGLISH_ID"]."] [".$english[$j]["ENGLISH_TEXT"]."] [".$french[$i]["FRENCH_ID"]."] [".$french[$i]["FRENCH_TEXT"]."]"; // Print the result set of the join. print_r($result_set); ?>
Assuming you save the program above as leftjoin.php in your file system, you can run it from the command-line interface with the following syntax:
php leftjoin.php
It would print the following 42 rows. You should note that indexes are zero-based numbers. The assignment to text rows was done to make the output more readable.
It would print the following 42 rows, remember the indexes are zero-based numbers. The alignment was manually adjusted to make it more readable.
Array
(
[0] => [1] [One] [1] [Un]
[1] => [2] [Two] [1] [Un]
[2] => [3] [Three] [1] [Un]
[3] => [4] [Four] [1] [Un]
[4] => [5] [Five] [1] [Un]
[5] => [6] [Six] [1] [Un]
[6] => [1] [One] [3] [Trois]
[7] => [2] [Two] [3] [Trois]
[8] => [3] [Three] [3] [Trois]
[9] => [4] [Four] [3] [Trois]
[10] => [5] [Five] [3] [Trois]
[11] => [6] [Six] [3] [Trois]
[12] => [1] [One] [4] [Quatre]
[13] => [2] [Two] [4] [Quatre]
[14] => [3] [Three] [4] [Quatre]
[15] => [4] [Four] [4] [Quatre]
[16] => [5] [Five] [4] [Quatre]
[17] => [6] [Six] [4] [Quatre]
[18] => [1] [One] [5] [Cinque]
[19] => [2] [Two] [5] [Cinque]
[20] => [3] [Three] [5] [Cinque]
[21] => [4] [Four] [5] [Cinque]
[22] => [5] [Five] [5] [Cinque]
[23] => [6] [Six] [5] [Cinque]
[24] => [1] [One] [6] [Six]
[25] => [2] [Two] [6] [Six]
[26] => [3] [Three] [6] [Six]
[27] => [4] [Four] [6] [Six]
[28] => [5] [Five] [6] [Six]
[29] => [6] [Six] [6] [Six]
[30] => [1] [One] [7] [Sept]
[31] => [2] [Two] [7] [Sept]
[32] => [3] [Three] [7] [Sept]
[33] => [4] [Four] [7] [Sept]
[34] => [5] [Five] [7] [Sept]
[35] => [6] [Six] [7] [Sept]
[36] => [1] [One] [8] [Huit]
[37] => [2] [Two] [8] [Huit]
[38] => [3] [Three] [8] [Huit]
[39] => [4] [Four] [8] [Huit]
[40] => [5] [Five] [8] [Huit]
[41] => [6] [Six] [8] [Huit])