SQL: Self Joins
Why is it sometimes we see SQL queries that list a table more than once in the same query. And this isn’t where you see a table in both the outer query, and also in a subquery. But when the same table is coded more than once in the same FROM clause. For example you might see:
FROM EMP E1 INNER JOIN ….
PROJ P INNER JOIN …
There are a number of reasons (logic wise) where this self-joining is needed in SQL, along with some performance gains. This article lists 3 of the more common reasons for seeing a table twice, along with descriptions and examples.
1. Self-Referencing Foreign Keys
2. Comparing rows in a table to other rows in the same table
3. When a lookup table is accessed from different values in the same query
The following descriptions and examples that follow are based on the set of Db2 sample tables (EMP, PROJ, DEPT).
- Self-Referencing Foreign Keys: