Database Tutorial

Course Tutorial Site

Site Admin

Cross Join

with 2 comments

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.

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])

Written by michaelmclaughlin

January 17th, 2010 at 10:25 pm

Posted in

2 Responses to 'Cross Join'

Subscribe to comments with RSS or TrackBack to 'Cross Join'.

  1. While looking at this, trying to understand HOW to do CROSS JOINS, it is more confusing trying to learn php to get help with the SQL. The basic concept is very easy to understand, but knowing how to implement it is not. I therefore suggest that you use MySQL or ORACLE examples in all of the JOIN sections. Otherwise those of us who have not taken php classes can still understand what you are striving to teach us.

    Tyler

    23 Feb 11 at 5:21 pm

  2. Tyler, the PHP code is an example of how the same type of problem is solved using imperative programming languages.

    michaelmclaughlin

    22 Oct 14 at 2:31 am

Leave a Reply