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.

Self Join Example ↓

Rule(s):

  1. A self join must have a join statement.
  2. A self join depends on table aliases to make copies of the same source table, and table aliases are critical writing self joins.
  3. A self join depends on table aliases to disambiguate columns that share the same name in different tables.
  4. A self join may use an INNER, LEFT, RIGHT, or FULL JOIN clause and must include a join statement in the FROM clause.
Self Join Syntax
ANSI SQL:89ANSI SQL:92

ANSI SQL:89

The following syntax examples let you perform a self join of two copies of one table in any database.

ANSI SQL:89 syntax does support an INNER JOIN. An INNER JOIN is an equijoin or filtered Cartesian Product. The filter matches values in one set with another based on equal values in one or more columns of each copy of the table in a self join.

1
2
3
4

SELECT   su1.system_user
,        su2.system_user
FROM     system_user su1, system_user su2
WHERE    su1.created_by = su2.system_user_id;

This returns the SYSTEM_USER column values for the primary row and the SYSTEM_USER column value for the row that created the row. It works because of a FOREIGN KEY column that points to a PRIMARY KEY column in the same table. Naturally, in this case the PRIMARY and FOREIGN KEY have different column names. All column names are unique in any table defintion.

ANSI SQL:92

The following syntax examples let you perform a self join of two copies of one table in any database.

ANSI SQL:89 syntax does support an INNER JOIN. An INNER JOIN is an equijoin or filtered Cartesian Product. The filter matches values in one set with another based on equal values in one or more columns of each copy of the table in a self join.

1
2
3
4

SELECT   su1.system_user
,        su2.system_user
FROM     system_user su1 INNER JOIN system_user su2
ON       su1.created_by = su2.system_user_id;

This returns the SYSTEM_USER column values for the primary row and the SYSTEM_USER column value for the row that created the row. It works because of a FOREIGN KEY column that points to a PRIMARY KEY column in the same table. Naturally, in this case the PRIMARY and FOREIGN KEY have different column names. All column names are unique in any table defintion.

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