A table can be joined to itself in a self join. In self join left and right tables are same with different alias name.If we need to create a result set that joins records in a table with other records in the same table, then we can use self join.
Example: I need to display all employee name along with their manager name.
Sample Emp table:
E_ID DEPT_ID E_NAME MANAGER_ID
1 10 A NULL
2 20 B 1
3 30 C 2
Query:
SELECT
a1.E_NAME
,b2.E_NAME AS MANAGER_NAME
FROM EMP a1
LEFT JOIN EMP b2
ON a1.MANAGER_ID = b2.E_ID
Output:
Example: I need to display all employee name along with their manager name.
Sample Emp table:
E_ID DEPT_ID E_NAME MANAGER_ID
1 10 A NULL
2 20 B 1
3 30 C 2
Query:
SELECT
a1.E_NAME
,b2.E_NAME AS MANAGER_NAME
FROM EMP a1
LEFT JOIN EMP b2
ON a1.MANAGER_ID = b2.E_ID
Output:
No comments:
Post a Comment