What is SELF JOIN and when would you use it

Question

What is self join and when would you use it  I don t understand self joins so a layman explanation with an example would be great

User · Answer

SQL self-join simply is a normal join which is used to join a table to itself   Example   Select   FROM Table t1  Table t2 WHERE t1 Id   t2 ID

User · Answer

You d use a self-join on a table that  refers  to itself - e g  a table of employees where managerid is a foreign-key to employeeid on that same table   Example   SELECT E name  ME name AS manager FROM dbo Employees E LEFT JOIN dbo Employees ME ON ME employeeid   E managerid

User · Answer

Well  one classic example is where you wanted to get a list of employees and their immediate managers   select e employee as employee  b employee as boss from emptable e  emptable b where e manager id   b empolyee id order by 1   It s basically used where there is any relationship between rows stored in the same table    employees  multi-level marketing  machine parts    And so on

User · Answer

You use a self join when a table references data in itself   E g   an Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee   To query the data and get information for both people in one row  you could self join like this   select e1 EmployeeID       e1 FirstName       e1 LastName      e1 SupervisorID       e2 FirstName as SupervisorFirstName       e2 LastName as SupervisorLastName from Employee e1 left outer join Employee e2 on e1 SupervisorID   e2 EmployeeID

User · Answer

A self join is simply when you join a table with itself  There is no SELF JOIN keyword  you just write an ordinary join where both tables involved in the join are the same table  One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous    It is useful when you want to correlate pairs of rows from the same table  for example a parent - child relationship  The following query returns the names of all immediate subcategories of the category  Kitchen    SELECT T2 name FROM category T1 JOIN category T2 ON T2 parent   T1 id WHERE T1 name    Kitchen

[sql] What is SELF JOIN and when would you use it?

Examples related to sql

Examples related to self-join