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.

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