About Join in Sql Server

Question

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

Manas Mohapatra · 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 Menoth · Answer

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 T · Answer

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/