Right Join
The RIGHT [OUTER] JOIN
matches every row in one table against every row in another based on common values in one or a set of columns, like an INNER JOIN
. It then also returns everything from the table on the right of the RIGHT JOIN
operator that was not returned as part of the match.
Another way to describe a RIGHT JOIN
is as an intersection between two rows of data plus all rows found in the right set not found in the left set. The formula uses a ∀ to indicate given any sets A and B, then A intersects (∩) B. The Venn diagram and set notation depicts that definition.
The syntax of a RIGHT OUTER JOIN
and RIGHT JOIN
are equivalent because the OUTER
keyword is optional in some databases and excluded in others. A RIGHT JOIN
is a filtered join of the conceptual Cartesian Product of two tables and a relative complement. The filter identifies the rows that have matching values in a pair of columns, or a pair of two or more column sets. The relative complement of the RIGHT JOIN
operator is the left relative complement.
A RIGHT JOIN
acts like a nested loop where you match a key from the outer loop against a key from the inner loop, while preserving all non-matching values from the outer loop. An example written in PHP is found at the bottom of this page.
SQL RIGHT JOIN Example →
Rule(s):
- A right join must have a join statement.
- A right join that uses the
RIGHT JOIN
clause must include a join statement in theFROM
clause. - A right join that uses comma delimited tables must include a join statement in the
WHERE
clause. - A right join returns the intersection of two sets and the left relative complement (everything in the right set not found in the left set).
Nested Loop Right Outer Join Product →
This is an example of how nested loops can produce the equivalent of a RIGHT [OUTER] JOIN
. As mentioned, a RIGHT JOIN
returns the intersection of rows based on matches of column values and the rows from the left-hand side of the join operator that found no matches.
Like the INNER JOIN
example, this sample code 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 RIGHT [OUTER] JOIN
uses a nested loop to match every return of the inner loop and an else statement to save non-matches. The second loop checks whether a non-match is found in the result set, and adds it when not found.
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | <?php // Declare a local control variable to pare interleaved values. $c = 0; // Declare four arrays, two with data, one matched data, and one for unmatched 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(); $outer_set = array(); $matched = false; // The outer loop s and inner loop the smaller array. for ($i = 0;$i < count($french);$i++) { // Merged joins work with sorted sets, and pare previous values; the ($i + $c) skips interleaved non-matches. for ($j = $i + $c;$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"]); $matched = true; break; } else // Excluding the Left Join logic. $c++; // Within the context of the outer loop capture values not found. if (!$matched) $outer_set[] = array("ENGLISH_ID" => null ,"ENGLISH_TEXT" => null ,"FRENCH_ID" => $french[$i]["FRENCH_ID"] ,"FRENCH_TEXT" => $french[$i]["FRENCH_TEXT"]); else $matched = false; } // Reads through all potential unmatched values. for ($i = 0;$i < count($outer_set);$i++) // Compares unmatched values to find unmatched values. for ($j = $i;$j < count($result_set);$j++) { if ($result_set[$j]["ENGLISH_ID"] != $outer_set[$i]["ENGLISH_ID"]) $result_set[] = $outer_set[$i]; // Discontinues search as there can only be one excluded from a join. break; } // Print the result set of the join. print_r($result_set); ?> |
Assuming you save the program above as rightjoin.php in your file system, you can run it from the command-line interface with the following syntax:
php rightjoin.php |
It would print the following 7 array elements. The first five elements are the result of the intersection or INNER JOIN
. The last two elements are the outer join result, which represents the relative complement of the table on the left side of the RIGHT JOIN
operator. A RIGHT JOIN
therefore returns the column values where two rows intersect on the values of one or a set of columns, and the relative complement of the table on the right hand side of the join operator.
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 ) [5] => Array ( [ENGLISH_ID] => [ENGLISH_TEXT] => [FRENCH_ID] => 7 [FRENCH_TEXT] => Sept ) [6] => Array ( [ENGLISH_ID] => [ENGLISH_TEXT] => [FRENCH_ID] => 8 [FRENCH_TEXT] => Huit ) ) |
Aren’t the last two numbers on the
ORDINAL_FRENCH_TEXT
supposed to beSept
andHuit
instead of seven and eight?According to your setup script on the “Multiple Table Query” page
Seven
andEight
don’t even show up in any of the tables, butSept
andHuit
show up in theORDINAL_FRENCH_TEXT
table.Brad Lawrence
13 Jul 11 at 9:12 am
Brad, Thanks, and they’re fixed now.
michaelmclaughlin
25 Oct 14 at 5:43 pm