We refer to a join as a self-join when…
-
We are joining two tables only
-
we are using left and right join together
-
we are joining table to itself
-
we are joining more than 2 tables
A self-join occurs when a table is joined to itself. This is typically done to compare rows within the same table, such as finding employees who report to each other (manager-subordinate relationships) or identifying duplicate records. The table appears in the FROM clause twice, usually with different aliases to distinguish the instances.
A self-join is precisely when a table is joined to itself, typically using two aliases of the same table so rows can be compared against other rows in that same table (e.g., finding employees and their managers within one employees table). Joining exactly two different tables is just a regular join, not a self-join; combining left and right joins is a different concept (full outer join behavior); and joining more than two tables is a multi-table join. None of those describe a self-join.