Three Table Self Join
A three table self join involves the join between two tables into a result set, and a second join of the result set to a third table. The join between the first two tables or the join between the result set and a third can use any of these join operators: INNER, LEFT, RIGHT, or FULL JOIN.
A table supports three table self joins when it contains a primary key and two foreign key columns. The system_user table in our video store model meets that criteria because it has:
- A primary key column –
system_user_id
- Two foreign key columns:
created_by and last_updated_by
A three table self join requires that:
- You need to join the
created_by foreign key column from the first copy of a table to the system_user_id primary key column of second copy of the table; and
- You need to join the
last_updated_by foreign key column from the first copy of a table to the system_user_id primary key column of third copy of the table.
The following illustrates the behavior of a three table self join of the system_user table:
The diagram shows:
- How the first
system_user copy’s created_by foreign key column points to the second system_user copy’s system_user_id primary key column.
- How the first
system_user copy’s last_updated_by foreign key column points to the third system_user copy’s system_user_id primary key column.
Three Table 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
5
6
7
8
9
10
| COLUMN system_user FORMAT A14 HEADING "System User"
COLUMN creating_user FORMAT A14 HEADING "Creating|User"
COLUMN updating_user FORMAT A14 HEADING "Updating|User"
SELECT su1.system_user AS "System User"
, su2.system_user AS "Creating User"
, su3.system_user AS "Updating User"
FROM system_user su1, system_user su2, system_user su3
WHERE su1.created_by = su2.system_user_id -- Primary(FK) to Secondary (PK)
AND su1.last_updated_by = su3.system_user_id -- Primary(FK) to Tertiary(PK)
ORDER BY su1.system_user_id; |
COLUMN system_user FORMAT A14 HEADING "System User"
COLUMN creating_user FORMAT A14 HEADING "Creating|User"
COLUMN updating_user FORMAT A14 HEADING "Updating|User"
SELECT su1.system_user AS "System User"
, su2.system_user AS "Creating User"
, su3.system_user AS "Updating User"
FROM system_user su1, system_user su2, system_user su3
WHERE su1.created_by = su2.system_user_id -- Primary(FK) to Secondary (PK)
AND su1.last_updated_by = su3.system_user_id -- Primary(FK) to Tertiary(PK)
ORDER BY su1.system_user_id;
It should return the following data set:
1
2
3
4
5
6
7
| System User Creating User Updating User
------------ -------------- --------------
SYSADMIN SYSADMIN SYSADMIN
DBA 1 SYSADMIN DBA 1
DBA 2 SYSADMIN SYSADMIN
DBA 3 SYSADMIN DBA 2
APPS DBA DBA 3 DBA 3 |
System User Creating User Updating User
------------ -------------- --------------
SYSADMIN SYSADMIN SYSADMIN
DBA 1 SYSADMIN DBA 1
DBA 2 SYSADMIN SYSADMIN
DBA 3 SYSADMIN DBA 2
APPS DBA DBA 3 DBA 3
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
5
6
7
8
9
| SELECT su1.system_user_name AS "System User"
, su2.system_user_name AS "Creating User"
, su3.system_user_name AS "Updating User"
FROM system_user su1
, system_user su2
, system_user su3
WHERE su1.created_by = su2.system_user_id -- Primary(FK) to Secondary (PK)
AND su1.last_updated_by = su3.system_user_id -- Primary(FK) to Tertiary(PK)
ORDER BY su1.system_user_id; |
SELECT su1.system_user_name AS "System User"
, su2.system_user_name AS "Creating User"
, su3.system_user_name AS "Updating User"
FROM system_user su1
, system_user su2
, system_user su3
WHERE su1.created_by = su2.system_user_id -- Primary(FK) to Secondary (PK)
AND su1.last_updated_by = su3.system_user_id -- Primary(FK) to Tertiary(PK)
ORDER BY su1.system_user_id;
It should return the following data set:
1
2
3
4
5
6
7
| System User Creating User Updating User
------------ -------------- --------------
SYSADMIN SYSADMIN SYSADMIN
DBA 1 SYSADMIN DBA 1
DBA 2 SYSADMIN SYSADMIN
DBA 3 SYSADMIN DBA 2
APPS DBA DBA 3 DBA 3 |
System User Creating User Updating User
------------ -------------- --------------
SYSADMIN SYSADMIN SYSADMIN
DBA 1 SYSADMIN DBA 1
DBA 2 SYSADMIN SYSADMIN
DBA 3 SYSADMIN DBA 2
APPS DBA DBA 3 DBA 3
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 KEY and FOREIGN KEY columns have different column names. All column names are unique in any table defintion.
