{"id":271,"date":"2018-04-25T18:19:30","date_gmt":"2018-04-25T11:19:30","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=271"},"modified":"2024-02-25T22:13:40","modified_gmt":"2024-02-25T15:13:40","slug":"sql-server-self-join","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-self-join\/","title":{"rendered":"SQL Server Self Join"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server self join to join a table to itself.<\/p>\n\n\n\n

SQL Server self join syntax #<\/a><\/h2>\n\n\n\n

A self join allows you to join a table to itself. It helps query hierarchical data or compare rows within the same table.<\/p>\n\n\n\n

A self join uses the inner join<\/a> or left join<\/a> clause. Because the query that uses the self join references the same table, the table alias<\/a> is used to assign different names to the same table within the query.<\/p>\n\n\n\n

Note that referencing the same table more than once in a query without using table aliases will result in an error.<\/p>\n\n\n\n

The following shows the syntax of joining the table T<\/code> to itself:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    T t1\n[INNER<\/span> | LEFT<\/span>]  JOIN<\/span> T t2 ON<\/span>\n    join_predicate; \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query references the table T<\/code> twice. The table aliases t1<\/code> and t2<\/code> are used to assign the T<\/code> table different names in the query.<\/p>\n\n\n\n
SQL Server self join examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples to understand how the self join works.<\/p>\n\n\n\n
1) Using self join to query hierarchical data #<\/a><\/h3>\n\n\n\n
Consider the following  staffs<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The  staffs<\/code> table stores the staff information such as id, first name, last name, and email. It also has a column named manager_id<\/code> that specifies the direct manager. For example, Mireya<\/code> reports to Fabiola<\/code> because the value in the manager_id<\/code> of  Mireya<\/code> is Fabiola<\/code>.<\/p>\n\n\n\n
Fabiola<\/code> has no manager, so the manager id column has a NULL.<\/p>\n\n\n\n
To get who reports to whom, you use the self join as shown in the following query:<\/p>\n\n\n
SELECT<\/span>\n    e.first_name + ' '<\/span> + e.last_name employee,\n    m.first_name + ' '<\/span> + m.last_name manager\nFROM<\/span>\n    sales.staffs e\nINNER<\/span> JOIN<\/span> sales.staffs m ON<\/span> m.staff_id = e.manager_id\nORDER<\/span> BY<\/span>\n    manager;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, we referenced to the \u00a0staffs<\/code> table twice: one as e<\/code> for the employees and the others as m<\/code> for the managers. The join predicate matches the employee and manager relationship using the values in the e.manager_id<\/code> and\u00a0m.staff_id<\/code> columns.<\/p>\n\n\n\n
The employee column does not have Fabiola Jackson<\/code> because of the INNER JOIN<\/a><\/code> effect. If you replace the INNER JOIN<\/a><\/code> clause by the LEFT JOIN<\/a><\/code> clause as shown in the following query, you will get the result set that includes Fabiola Jackson<\/code> in the employee column:<\/p>\n\n\n
SELECT<\/span>\n    e.first_name + ' '<\/span> + e.last_name employee,\n    m.first_name + ' '<\/span> + m.last_name manager\nFROM<\/span>\n    sales.staffs e\nLEFT<\/span> JOIN<\/span> sales.staffs m ON<\/span> m.staff_id = e.manager_id\nORDER<\/span> BY<\/span>\n    manager;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
2) Using self join to compare rows within a table #<\/a><\/h3>\n\n\n\n
See the following customers<\/code> table:<\/p>\n\n\n\n
\"customers<\/figure>\n\n\n\n
The following statement uses the self join to find the customers located in the same city.<\/p>\n\n\n
SELECT<\/span>\n    c1.city,\n    c1.first_name + ' '<\/span> + c1.last_name customer_1,\n    c2.first_name + ' '<\/span> + c2.last_name customer_2\nFROM<\/span>\n    sales.customers c1\nINNER<\/span> JOIN<\/span> sales.customers c2 ON<\/span> c1.customer_id > c2.customer_id\nAND<\/span> c1.city = c2.city\nORDER<\/span> BY<\/span>\n    city,\n    customer_1,\n    customer_2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The following condition makes sure that the statement doesn’t compare the same customer:<\/p>\n\n\n
c1.customer_id > c2.customer_id<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following condition matches the city of the two customers:<\/p>\n\n\n
AND c1.city = c2.city<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that if you change the greater than ( > ) operator by the not equal to (<>) operator, you will get more rows:<\/p>\n\n\n
SELECT<\/span>\n    c1.city,\n    c1.first_name + ' '<\/span> + c1.last_name customer_1,\n    c2.first_name + ' '<\/span> + c2.last_name customer_2\nFROM<\/span>\n    sales.customers c1\nINNER<\/span> JOIN<\/span> sales.customers c2 ON<\/span> c1.customer_id <> c2.customer_id\nAND<\/span> c1.city = c2.city\nORDER<\/span> BY<\/span>\n    city,\n    customer_1,\n    customer_2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Let’s see the difference between > and <> in the ON<\/code> clause by limiting to one city to make it easier for comparison.<\/p>\n\n\n\n
The following query returns the customers located in Albany:<\/p>\n\n\n
SELECT<\/span> \n   customer_id, first_name + ' '<\/span> + last_name c, \n   city\nFROM<\/span> \n   sales.customers\nWHERE<\/span>\n   city = 'Albany'<\/span>\nORDER<\/span> BY<\/span> \n   c;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
This query uses ( ><\/code>) operator in the ON<\/code> clause:<\/p>\n\n\n
SELECT<\/span>\n    c1.city,\n    c1.first_name + ' '<\/span> + c1.last_name customer_1,\n    c2.first_name + ' '<\/span> + c2.last_name customer_2\nFROM<\/span>\n    sales.customers c1\nINNER<\/span> JOIN<\/span> sales.customers c2 ON<\/span> c1.customer_id > c2.customer_id\nAND<\/span> c1.city = c2.city\nWHERE<\/span> c1.city = 'Albany'<\/span>\nORDER<\/span> BY<\/span>\n    c1.city,\n    customer_1,\n    customer_2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
This query uses ( <><\/code>) operator in the ON<\/code> clause:<\/p>\n\n\n
SELECT<\/span>\n    c1.city,\n\tc1.first_name + ' '<\/span> + c1.last_name customer_1,\n    c2.first_name + ' '<\/span> + c2.last_name customer_2\nFROM<\/span>\n    sales.customers c1\nINNER<\/span> JOIN<\/span> sales.customers c2 ON<\/span> c1.customer_id <> c2.customer_id\nAND<\/span> c1.city = c2.city\nWHERE<\/span> c1.city = 'Albany'<\/span>\nORDER<\/span> BY<\/span>\n\tc1.city,\n    customer_1,\n    customer_2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use an SQL Server self join to query hierarchical data and compare rows in the same table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t