Inner Join
The INNER JOIN
matches every row in one table against every row in another based on common values in one or a set of columns found in both tables. This type of join operation is a filtered join. In its most basic approach an INNER JOIN
acts like a nested loop where you match a key from the outer loop against a key from the inner loop.
Another way to describe an INNER JOIN
is as an intersection between two rows of data. The formula uses a ∀ to indicate given any sets A and B, then A intersects (∩) B. The intersection works by 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. In some implementations you get two copies of the column or set of column values that match, and in others you get a single copy of the matching column or set of column values.
SQL INNER JOIN Example →
Rule(s):
- An inner join must have a join statement.
- An inner join that uses the
INNER JOIN
clause must include a join statement in theFROM
clause. - An inner join that uses comma delimited tables must include a join statement in the
WHERE
clause. - An inner join returns the intersection of two sets.
Nested Loop Inner Join Product →
This is an example of how nested loops can produce an intersection of two sets, or an inner join of two sets. This employs a merged join. Merged joins require that you pre-order the arrays that you want to join. These base sets are already organized as ascending ordered sets.
You should notice that a INNER JOIN
uses a nested loop to match every return of the inner loop with a return of the outer loop, like the Cartesian example. It differs inside the nested loop by only assigning those values that match, which is the equivalent of an INNER JOIN
. Line 22 contains an if
statement that matches the column values.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | <?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 = $i;$j < count($english);$j++) // Perform a match for an intersection or inner join of two array sets. if ($english[$j]["ENGLISH_ID"] == $french[$i]["FRENCH_ID"]) // This selects the elements from the cross joined set, like a SELECT clause in SQL. $result_set[] = array("ENGLISH_ID" => $english[$j]["ENGLISH_ID"] ,"ENGLISH_TEXT" => $english[$j]["ENGLISH_TEXT"] ,"FRENCH_ID" => $french[$i]["FRENCH_ID"] ,"FRENCH_TEXT" => $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 5 array elements. The elements are the result of the intersection or INNER JOIN
.
Array ( [0] => Array ( [ENGLISH_ID] => 1 [ENGLISH_TEXT] => One [FRENCH_ID] => 1 [FRENCH_TEXT] => Un ) [1] => Array ( [ENGLISH_ID] => 3 [ENGLISH_TEXT] => Three [FRENCH_ID] => 3 [FRENCH_TEXT] => Trois ) [2] => Array ( [ENGLISH_ID] => 4 [ENGLISH_TEXT] => Four [FRENCH_ID] => 4 [FRENCH_TEXT] => Quatre ) [3] => Array ( [ENGLISH_ID] => 5 [ENGLISH_TEXT] => Five [FRENCH_ID] => 5 [FRENCH_TEXT] => Cinque ) [4] => Array ( [ENGLISH_ID] => 6 [ENGLISH_TEXT] => Six [FRENCH_ID] => 6 [FRENCH_TEXT] => Six ) ) |
You could improve the match by changing the logic from a nested loop to a Blocked Nested Loop (BNL), like the following program unit. There are also many more robust algorithms, that improve search and match operations.
This particular example uses a resolution between three copies of the same set to illustrate how the outer loop only navigates once while each inner loop processes one-time for each element in the outer loop.
<?php // Declare two arrays, two with data and one for the combined data. $su = array(array("SYSTEM_USER_ID"=>1,"SYSTEM_USER_NAME"=>"SYSADMIN","CREATED_BY"=>1,"LAST_UPDATED_BY"=>1) ,array("SYSTEM_USER_ID"=>2,"SYSTEM_USER_NAME"=>"DBA 1","CREATED_BY"=>1,"LAST_UPDATED_BY"=>2) ,array("SYSTEM_USER_ID"=>3,"SYSTEM_USER_NAME"=>"DBA 2","CREATED_BY"=>1,"LAST_UPDATED_BY"=>1) ,array("SYSTEM_USER_ID"=>4,"SYSTEM_USER_NAME"=>"DBA 3","CREATED_BY"=>1,"LAST_UPDATED_BY"=>3) ,array("SYSTEM_USER_ID"=>5,"SYSTEM_USER_NAME"=>"APPS DBA","CREATED_BY"=>4,"LAST_UPDATED_BY"=>4)); // Assign aliases to the single array set. $su1 = $su; $su2 = $su; $su3 = $su; // In self join they're all equal. for ($i = 0;$i < count($su);$i++) { for ($j = 0;$j < count($su);$j++) // Perform a match for an intersection or inner join of the two sets on a primary to foreign key match. if ($su2[$j]["SYSTEM_USER_ID"] == $su1[$i]["CREATED_BY"]) // This selects the elements from the joined set, like the SELECT clause in SQL. $result_set[$i] = "[".$su1[$i]["SYSTEM_USER_ID"]."] [".$su1[$i]["SYSTEM_USER_NAME"]."] [".$su2[$j]["SYSTEM_USER_NAME"]."]"; for ($j = 0;$j < count($su);$j++) // Perform a match for an intersection or inner join of the two sets on a primary to foreign key match. if ($su3[$j]["SYSTEM_USER_ID"] == $su1[$i]["LAST_UPDATED_BY"]) // This selects the elements from the joined set, like the SELECT clause in SQL. $result_set[$i] .= " [".$su3[$j]["SYSTEM_USER_NAME"]."]"; } // Print the result set of the join. print_r($result_set); ?> |
The results are not shown but you can test it on your own to see how it works.