Database Tutorial

Course Tutorial Site

Site Admin

Right Join

with 2 comments

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

  1. A right join must have a join statement.
  2. A right join that uses the RIGHT JOIN clause must include a join statement in the FROM clause.
  3. A right join that uses comma delimited tables must include a join statement in the WHERE clause.
  4. 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).
RIGHT JOIN Syntax
ANSI SQL:89ANSI SQL:92

ANSI SQL:89

The following syntax examples let you perform a RIGHT JOIN of two tables in an Oracle database.

ANSI SQL:89 syntax doesn’t support a RIGHT [OUTER] JOIN. Oracle Corporation noted this problem and implemented an extension to ANSI SQL:89, which is known as Oracle Proprietary SQL (e.g., dubbed as such by Alice Rischert in Oracle SQL by Example).

You work with this Oracle Proprietary extension by putting the table names in the FROM clause as a comma delimited list. There’s no requirement to implicitly or explicitly order these table names. The join statement(s) occurs in the WHERE clause. You can compare the equality of values between one column from each table, or a set of column from each table. In the case of a set of columns, you use the AND logical operator to broaden the comparison of values. You may also use table aliases to effect a self-join.

Oracle’s Proprietary extension uses a (+) operator, which supports RIGHT JOIN and LEFT JOIN but not FULL JOIN. At least you can’t support a FULL JOIN without also using set operators.

The (+) following the ordinal_english_id column in the join indicates that you want the relative complement of that table, which are all non-matching values in the ordinal_french table. Basically, the relative complement of the table on the left of the join points to all values in the table on the right of the join, which is the mathematical way to view the problem. This moreover is a RIGHT JOIN.

The following syntax lets you right join two tables:

1
2
3
4

SELECT   ordinal_english_text
,        ordinal_french_text
FROM     ordinal_english, ordinal_french
WHERE    ordinal_english_id(+) = ordinal_french_id;

This returns only rows with only the two selected columns, where they are the result of a join between to tables or a row exists in the ORDINAL_ENGLISH table that isn’t found in the ORDINAL_FRENCH table.

ORDINAL_ENGLISH_TEXT ORDINAL_FRENCH_TEXT
-------------------- --------------------
One                  Un
Three                Trois
Four                 Quatre
Five                 Cinq
Six                  Six
                     Sept
                     Huit

As mentioned, this is the older syntax and it is proptietary to Oracle SQL dialect because there wasn’t any way to capture the outer join values until the ANSI SQL:92 specification.

ANSI SQL:92

The following syntax lets you right join two tables:

ANSI SQL:92 syntax effects an RIGHT [OUTER] JOIN by using an ON subclause or a USING subclause in the FROM clause. It returns the intersection of rows that share matching column values. It also returns the rows from the table on the right of the join that aren’t found in the table on the left.

As mentioned, the OUTER keyword is optional and generally only used by tools or for added clarity by developers. The ON subclause tests the equality of values between columns where the column names may be the same or different. The USING clause tests the equality of two columns when they have the same name.

The ON subclause also lets you use an AND logical operator to support multi-column joins. You may also provide multiple column names as call parameters to the USING clause because it works more like a function than a clause.

The following syntax lets you RIGHT JOIN between two tables with the ON subclause:

1
2
3
4

SELECT   ordinal_english_text
,        ordinal_french_text
FROM     ordinal_english RIGHT OUTER JOIN ordinal_french
ON       ordinal_english_id = ordinal_french_id;

This returns only rows with only the two selected columns, where they are the result of a join between two tables. Specifically, a row or set of rows that exists in the ORDINAL_FRENCH table that aren’t found in the ORDINAL_ENGLISH table.

ORDINAL_ENGLISH_TEXT ORDINAL_FRENCH_TEXT
-------------------- --------------------
One                  Un
Three                Trois
Four                 Quatre
Five                 Cinq
Six                  Six
                     Sept
                     Huit

The ORDINAL_FRENCH table’s primary key column name differs from the ORDINAL_ENGLISH table’s primary key column name. The difference requires that you use the ON subclause.

As mentioned, you can use the USING subclause when the column names are equal. You can test this by changing the second table to the ORDINAL_OLD_ENGLISH table. They both enjoy the same primary key column name.

The following demonstrates a RIGHT JOIN that uses the USING subclause

1
2
3

SELECT   ordinal_english_text
,        ordinal_old_english_text
FROM     ordinal_english RIGHT JOIN ordinal_old_english USING(ordinal_english_id);

This returns the same type of values seen above, except for that you see old English words instead of French words (:-)).

As mentioned, you can put more than one column name in a USING clause. There may be an implementation limit but I’ve not found it. It appears that the parser treats the list recursively. The syntax for that approach is the following:

USING(column_name1, column_name2, ...)

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

Written by michaelmclaughlin

January 17th, 2010 at 10:25 pm

Posted in

2 Responses to 'Right Join'

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

  1. Aren’t the last two numbers on the ORDINAL_FRENCH_TEXT supposed to be Sept and Huit instead of seven and eight?
    According to your setup script on the “Multiple Table Query” page Seven and Eight don’t even show up in any of the tables, but Sept and Huit show up in the ORDINAL_FRENCH_TEXT table.

    Brad Lawrence

    13 Jul 11 at 9:12 am

  2. Brad, Thanks, and they’re fixed now.

    michaelmclaughlin

    25 Oct 14 at 5:43 pm

Leave a Reply