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):
- A self join must have a join statement.
- A self join depends on table aliases to make copies of the same source table, and table aliases are critical writing self joins.
- A self join depends on table aliases to disambiguate columns that share the same name in different tables.
- A self join may use an
INNER
, LEFT
, RIGHT
, or FULL JOIN
clause and must include a join statement in the FROM
clause.
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; |
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; |
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.
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
Sure, in this example you’re discovering who gave access to somebody. It’s done by leveraging the “who-audit” columns of
CREATED_BY
andLAST_UPDATED_BY
. These two columns can be tied to the same or different rows in theSYSTEM_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
Thanks.
Josiah
5 Feb 10 at 1:42 pm
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
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
Rule number four doesn’t read very well. It sounds like it should say
“A self join may use an an
INNER
,LEFT
,RIGHT
, orFULL JOIN
clause **and** must include a join statement in theFROM
clause.”It also could be two sentences.
Sam Graham
18 Oct 14 at 5:18 pm
Sam, Thanks. I’ve added the and, and removed the second an earlier in the sentence.
michaelmclaughlin
22 Oct 14 at 12:36 am