Database Tutorial

Course Tutorial Site

Site Admin

Full Join

with 4 comments

The FULL [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 left and right sides of the FULL JOIN operator that weren’t found in the other respective table.

Another way to describe a FULL JOIN is as an intersection between two rows of data plus all rows found in the left set not found in the right set and 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 FULL OUTER JOIN and FULL JOIN are equivalent because the OUTER keyword is optional in some databases and excluded in others. A FULL 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 complements of the FULL JOIN operator are the the additive sum of the left and right relative complements, which is known as the symmetric difference of the sets.

A FULL 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 both original arrays. An example written in PHP is found at the bottom of this page.

SQL FULL JOIN Example →

Rule(s):

  1. A full join must have a join statement.
  2. A full join that uses the FULL JOIN clause must include a join statement in the FROM clause.
  3. A full join that uses comma delimited tables must include a join statement in the WHERE clauses of two queries that produce mirrored sets; and the sets are joined by a UNION set operator.
  4. A full join returns the intersection of two sets and both the left relative complement (everything in the right set not found in the left side set) and right relative complement (everything in the left set not found in the right side set).
FULL JOIN Syntax
ANSI SQL:89ANSI SQL:92

ANSI SQL:89

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

ANSI SQL:89 syntax doesn’t support a FULL [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 either a LEFT JOIN or RIGHT JOIN. When you perform a combine the return sets of a LEFT JOIN and RIGHT JOIN through a UNION operator, you get the equivalent of a FULL JOIN. This works because the two copies of the INNER JOIN are reduced to a unique set as part of the sorting operation performed by the UNION operator.

LEFT JOIN Component

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

RIGHT JOIN Component

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 bind together through a set operation the LEFT JOIN and RIGHT JOIN. As mentioned, the UNION operator sorts and removes duplicates from the two INNER JOIN operations, which are part of the LEFT JOIN and RIGHT JOIN operations.

1
2
3
4
5
6
7
8
9

SELECT   ordinal_english_text
,        ordinal_french_text
FROM     ordinal_english, ordinal_french
WHERE    ordinal_english_id = ordinal_french_id(+)
UNION
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
Two
                     Seven
                     Eight

As mentioned, this is the older syntax and it is proprietary 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 FULL JOIN two tables:

ANSI SQL:92 syntax effects an FULL [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 left and right sides of the join that aren’t found in their respective other table in the context of the join operation.

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 (or two sets of multiple 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 FULL JOIN between two tables with the ON subclause:

1
2
3
4

SELECT   ordinal_english_text
,        ordinal_french_text
FROM     ordinal_english FULL 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 both the ORDINAL_FRENCH and ORDINAL_ENGLISH tables based on the match of the intersection or INNER JOIN and the semmetric difference, which is composed of the left and right relative complements.

ORDINAL_ENGLISH_TEXT ORDINAL_FRENCH_TEXT
-------------------- --------------------
One                  Un
Three                Trois
Four                 Quatre
Five                 Cinq
Six                  Six
Two
                     Seven
                     Eight

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 FULL JOIN that uses the USING subclause

1
2
3

SELECT   ordinal_english_text
,        ordinal_old_english_text
FROM     ordinal_english FULL 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 Full Outer Join Product →

This is an example of how nested loops can produce the equivalent of a FULL [OUTER] JOIN. As mentioned, a FULL JOIN returns the intersection of rows based on matches of column values and the rows from the left- and right-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 FULL [OUTER] JOIN uses a nested loop to match every return of the inner loop and an else statement to save non-matches. A second nested loop captures the outer loop values that weren’t found in the inner loop.

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
61
62
63

<?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 {
        $outer_set[] = array("ENGLISH_ID" => $english[$j]["ENGLISH_ID"]
                            ,"ENGLISH_TEXT" => $english[$j]["ENGLISH_TEXT"]
                            ,"FRENCH_ID" => null
                            ,"FRENCH_TEXT" => null);
        $c++; }
 
      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 fulljoin.php in your file system, you can run it from the command-line interface with the following syntax:

php fulljoin.php

It would print the following 6 array elements. The first five elements are the result of the intersection or INNER JOIN. The last element is a the outer join result, which represents the relative complement of the table on the right side of the LEFT JOIN operator. A LEFT 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] => 2
            [ENGLISH_TEXT] => Two
            [FRENCH_ID] =>
            [FRENCH_TEXT] =>
        )
 
    [6] => Array
        (
            [ENGLISH_ID] =>
            [ENGLISH_TEXT] =>
            [FRENCH_ID] => 7
            [FRENCH_TEXT] => Sept
        )
 
    [7] => Array
        (
            [ENGLISH_ID] =>
            [ENGLISH_TEXT] =>
            [FRENCH_ID] => 8
            [FRENCH_TEXT] => Huit
        )
 
)

Written by michaelmclaughlin

January 17th, 2010 at 10:26 pm

Posted in

4 Responses to 'Full Join'

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

  1. Couldn’t you also simply write a full join as?

    ∀ A , B | A ∪ B

    Chris Staber

    15 Oct 10 at 12:06 pm

  2. Yes, but then most students wouldn’t know that the relative complements are in a union. However, that’s a great observation on Axiomatic Set Theory. 🙂

    michaelmclaughlin

    18 Dec 10 at 11:49 pm

  3. In your discussion of Full Joins, you forget to mention the fact that mysql doesn’t support full join ANSI 92 syntax, but can work around that with a Union Operator and a Right Join query with a Left Join query.

    James

    7 Feb 14 at 6:49 pm

  4. James, That’s true, and I’ll be updating the site next term. Thanks.

    michaelmclaughlin

    18 Mar 14 at 10:58 am

Leave a Reply