Understanding SQL Server SELF JOIN By Practical Examples
What is SELF JOIN and how does it work?
The SELF JOIN in SQL, as its name implies, is used to join a table to itself. This means that each row in a table is joined to itself and every other row in that table. However, referencing the same table more than once within a single query will result in an error. To avoid this, SQL SELF JOIN aliases are used.
SELF JOIN syntax
To perform a SELF JOIN in SQL, the LEFT or INNER JOIN is usually used.
SELECT column_names FROM Table1 t1 [INNER | LEFT] JOIN Table1 t2 ON join_predicate;
Note: t1 and t2 are different table aliases for the same table.
You can also create the SELF JOIN with the help of the WHERE clause.
SELECT column_names FROM Table1 t1, Table1 t2 WHERE condition;