About Join in Sql Server

Gaurav Mehra

10y855

Why We use Self Join in Sql Server? What are the benefits of Self Join?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Accepted answer
Self Join: It is used to join table to itself. Means it joins between two columns of same table.

It is helpful when you have hierarchical value in a table. For instance, there is one table called employee table.

Employee

Id Name MgrId Designation
1 Mgr 0 Manager
2 Gaurav 1 Analyst
3 Manas 2 Developer

SELECT e2.Id, e2.Name EmployeeName, e1.Name as MangerName
FROM Employee e1 INNER JOIN Employee e2
ON e1.Id = e2.MgrId;
Rajeesh Menoth10y ago
Hi,

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

Example With Demo :

http://www.w3resource.com/sql/joins/perform-a-self-join.php

Reference :

http://www.c-sharpcorner.com/Blogs/11834/self-joins-in-sql-server.aspx
Raja T10y ago
Hello Gaurav, Manas provided is correct. If you want learn more details check below links

Self Join
Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

http://www.aspdotnet-suresh.com/2012/07/self-join-query-in-sql-server-self-join.html

http://www.c-sharpcorner.com/Blogs/11834/self-joins-in-sql-server.aspx

http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/