What is SELF JOIN and when do you use it?

Question

Possible duplicate:
sql: self-explanatory explanations

What is self-join and when do you use it? I don’t understand that I am joining, so the layman’s explanation with an example will be wonderful.

Answer 1

You use self-join when a table refers to data by itself.

For example, an Employee table may have a SupervisorID column that points to an employee who is the boss of the current employee.

To request data and get information for people in one line, you can independently join 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 

Answer 2

Well, one classic example is where you wanted to get a list of employees and their direct 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 is mainly used where there is any connection between the rows stored in the same table.

• employees.
• multi-level marketing.
• machine parts.

And so on...

Answer 3

Self-joining is easy when you join a table with yourself. There is no SELF JOIN keyword, you just write a regular join, where both tables involved in the join are the same table. One thing to note: when you join yourself, you must use an alias for the table, otherwise the table name will be ambiguous.

This is useful when you want to match pairs of rows from the same table, such as parent-child relationships. The following query returns the names of all the nearest subcategories of the Kitchen category.

 SELECT T2.name FROM category T1 JOIN category T2 ON T2.parent = T1.id WHERE T1.name = 'Kitchen' 

Answer 4

SQL self-join is simply a regular join, which is used to join the table to itself.

Example:

 Select * FROM Table t1, Table t2 WHERE t1.Id = t2.ID 

Answer 5

You should use self-join in a table that "references" itself - for example. The employee table, where managerid is the foreign key for employeeid in the same table.

Example:

 SELECT E.name, ME.name AS manager FROM dbo.Employees E LEFT JOIN dbo.Employees ME ON ME.employeeid = E.managerid