SQL Server Self Join

Summary: in this tutorial, you will learn how to use the SQL Server self join to join a table to itself.

SQL Server self join syntax #

A self join allows you to join a table to itself. It helps query hierarchical data or compare rows within the same table.

A self join uses the inner join or left join clause. Because the query that uses the self join references the same table, the table alias is used to assign different names to the same table within the query.

Note that referencing the same table more than once in a query without using table aliases will result in an error.

The following shows the syntax of joining the table T to itself:

SELECT
    select_list
FROM
    T t1
[INNER | LEFT]  JOIN T t2 ON
    join_predicate; 
Code language: SQL (Structured Query Language) (sql)

The query references the table T twice. The table aliases t1 and t2 are used to assign the T table different names in the query.

SQL Server self join examples #

Let’s take some examples to understand how the self join works.

1) Using self join to query hierarchical data #

Consider the following  staffs table from the sample database: