self join in sql
/* SELF JOIN - Joining a table with itself is called as self join.
It is classified under any type of join.
INNER JOIN
OUTER JOIN
CROSS JOIN. */
SELECT Column_List
FROM Left_Table_Name AS Alias_Name
JOIN_TYPE Right_Table_Name AS Alias_Name
ON Alias_Name.Column_List [Operator] Alias_Name.Column_List
/* Example*/
SELECT E.Name AS Employee, M.Name AS Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID
4
1
Self is joining a table to itself.
-- assume employee table as 2 different table using different alias
-- as manager and worker
-- we want to join these 2 virtual manager and worker table
-- to get manager's first name and worker's first name
-- our condition is worker's manager_id match managers employee id
SELECT manager.FIRST_NAME AS MANAGER_NAME ,
worker.FIRST_NAME AS WORKER_NAME
FROM EMPLOYEES manager
INNER JOIN EMPLOYEES worker on worker.MANAGER_ID = manager.EMPLOYEE_ID
order by 1
;
Thank you!
1
4
(1 Votes)
0
4
7
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
Thank you!
7
4
(7 Votes)
0
Are there any code examples left?
New code examples in category SQL
