SELF JOIN allows you to join a table to itself.
It is useful for querying hierarchical data or comparing rows within the
same table.
SELF JOIN uses the INNER JOIN or LEFT JOIN clause.
Because the query using the SELF JOIN references the same table,
the table alias is used to assign different names to the same table within
a query.
Note that referencing the same table more than once in a query without
using table aliases will result in an error.
Here is a SELECT with the syntax of joining table T with itself:
SELECT select_list FROM T t1 [INNER | LEFT] JOIN T t2 ON
join_predicate;
The query references table T twice. The table aliases t1 and t2 are used
to give table T different names in the query.
SQL Server Self Join Example
Consider the following table of staffs from the sample database:
Using SELF JOIN to query hierarchical data
The student table stores the name information of each student, as well as
its identification number.
It also has a column called idSupervisor that specifies
the direct academic supervisor. For example, Ann reports to supervisor
Christopher.
Cristopher has no supervisor, so the supervisor id column has a NULL.
To get who reports to whom, we will use SELF JOIN as shown in the
following query:
SELECT * FROM dbo.student AS S1 INNER JOIN dbo.student AS S2 ON
S1.idStudent = S2.idSupervisor;
And the result would be the following:
In this example, the student table was referenced twice.
The union predicate coincides with the relationship between student and
supervisor, in the understanding that the most outstanding students are
supervisors.
The S1 column does not have Ann or Bea due to the INNER JOIN
effect.
If you replace the INNER JOIN clause with the LEFT JOIN
clause as shown in the following query, you will get the result set that
includes all the records.
SELECT * FROM dbo.student AS S1 LEFT JOIN dbo.student AS S2 ON
S1.idStudent = S2.idSupervisor;
In this tutorial, you have learned how to use MySQL Server’s SELF
JOIN to query hierarchical data in a single table.