Two Table Self Join
A two table self join uses two copies of a single table. The join can use an INNER, LEFT, RIGHT, or FULL JOIN.
A table supports a two table self join when it contains a primary key and a foreign key column. You use the relationship between the primary key and foreign key columns to map the relationship between the two copies of the same table.
While this particular table would support a three table self join, it also supports two possible two table self joins. You join the two tables by mapping the created_by column to the system_user_id primary key, or by mapping the last_updated_by foreign key column to the system_user_id primary key.
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.
