Register now or log in to join your professional community.
Self join means you are comparing a table to itself, not to other tables.
For example:
Lets say you have a table called employees with ID, Name and MangerID. and you want know who is the manger of each Name.
| ID | NAME | MangerID
|1 | Abby | Null
|2 | Rubi |1
|3 | Trey |1
|4 | Mace |2
|5 | Zack |2
|6 | Mark |5
|7 | Kaci |5
Self Join SQL Query to get the names of manager and names:
SELECT e2.Name AS Employee, e1.Name AS Manger
FROM employees e1 INNER JOIN employees e2
ON e1.ID = e2.MangerID
Result:
Employee | Manger
Abby | Null
Rubi | Abby
Trey | Abby
Mace | Rubi
Zack | Rubi
Mark | Zack
Kaci | Zack
In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.
FROM employees e1 INNER JOIN employees e2
ON e1.ID = e2.MangerID
Here e2.MangerID passes the manager id from the2nd view to the first aliased e1 table to get the names of managers.
Self Join, use to fetch record from same table refer to two or more different fields. example of employee table in Oracle for scott user. If we need to know who the Manager name of each employee, its required to use Self Join as employee Id is used to define the Manager also.
JOIN TABLE ITSELF..
EX
select
e.ename,e1.ename
from
emp e,e1
where e.deptno=e1.deptno;
SQL SELF JOIN is used to join a table to itself
A common use is when the table stores records which have a hierarchical relationshiplike
Category Table with fields Id, Name and ParentCatId
Here ParentCatId is Id itself [ hierarchical relationship]
to get child and parent here we can use SELF JOIN.
In a self join we are joining the same table to itself by essentially creating two copies of that table.
example:
SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;
as you see the table "CUSTOMERS" was called2 times with different aliases a & b
Self Join means, the same table is getting compared in WHERE condition having alias to make it as a different table.
Example Employee table to find the manager of the employees. here manager is also a employee and his subordinates also employees,
--from employee e, employee m where e.id=m.id (like that depends upon the requirement)
SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Example:
SELECT a.column_name, b.column_name... FROM table1 a, table1 b WHERE a.common_field = b.common_field;
Self Join: Join with Itself.
Use of 'Self Join':for example; you want to fetch/display some information in one row when originally this information stored in two different rows of a table BUT this information should have something common.
Self-join
A self-join is joining a table to itself.
A query to find all pairings of two employees in the same country is desired. If there were two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, a normal join operation could be used to find the answer table. However, all the employee information is contained within a single large table.
