A table can be joined to itself and this is called a self-join. You can use a self-join to create a result set that joins records in a table with other records in the same table for example to filter a specific subset of records from within a single table without returning duplicate or similar rows.
Self-joins are useful in queries or subqueries such as the following:
- a limited result set of rows is needed
- a relationship between rows is needed
- a calculation among rows is needed
Self-joins are also useful when converting vendor specific syntax to vendor agnostic syntax
References
