Database Tutorial

Course Tutorial Site

Site Admin

Self Joins

with 7 comments

A self join can be an INNER, LEFT, RIGHT, or FULL JOIN. A table supports a self join when it contains a PRIMARY KEY and FOREIGN KEY that points to it’s own PRIMARY KEY. This means you can join a row in the table to the same row or another row in the table, and the join is wholly dependent on the matching of values in the PRIMARY and FOREIGN KEY column(s).

A self join acts like a nested loop where you match a key from the outer loop against a key from the inner loop. The trick is that the outer and inner loop hold different copies of the same data set. You effectively tie together two rows into one master set of information, much as you would rows from different tables in any other join operation.

Written by michaelmclaughlin

January 18th, 2010 at 10:21 pm

Posted in

7 Responses to 'Self Joins'

Subscribe to comments with RSS or TrackBack to 'Self Joins'.

  1. I believe I understand the concept and how to implement a self join, but is there an example of a ‘real-world’ situation that would call for a self join? I’m just drawing a blank as to when I would use this.

    Josiah

    5 Feb 10 at 12:50 pm

  2. Sure, in this example you’re discovering who gave access to somebody. It’s done by leveraging the “who-audit” columns of CREATED_BY and LAST_UPDATED_BY. These two columns can be tied to the same or different rows in the SYSTEM_USER table. Any attempt to find both the creator or last updator of a row in the table requires a self-join to link the two rows together.

    You can also check the hierarchical join mechanics for an organization to see another concrete example.

    michaelmclaughlin

    5 Feb 10 at 1:03 pm

  3. Thanks.

    Josiah

    5 Feb 10 at 1:42 pm

  4. I don’t know if there is an error with my browser or not, but it would be helpful if you could see the resulting table from the inner join to help tie the knots as to what everything is.

    Zach Morris

    4 Feb 14 at 5:13 pm

  5. Zach, that requires building in a query to another MySQL database or reducing it to JavaScript. It’s on the list but a low priority for the moment. Thanks.

    michaelmclaughlin

    18 Mar 14 at 11:02 am

  6. Rule number four doesn’t read very well. It sounds like it should say

    “A self join may use an an INNER, LEFT, RIGHT, or FULL JOIN clause **and** must include a join statement in the FROM clause.”

    It also could be two sentences.

    Sam Graham

    18 Oct 14 at 5:18 pm

  7. Sam, Thanks. I’ve added the and, and removed the second an earlier in the sentence.

    michaelmclaughlin

    22 Oct 14 at 12:36 am

Leave a Reply