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.

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