1/* SELF JOIN - Joining a table with itself is called as self join.
2 It is classified under any type of join.
3 INNER JOIN
4 OUTER JOIN
5CROSS JOIN. */
6SELECT Column_List
7FROM Left_Table_Name AS Alias_Name
8JOIN_TYPE Right_Table_Name AS Alias_Name
9ON Alias_Name.Column_List [Operator] Alias_Name.Column_List
10
11/* Example*/
12SELECT E.Name AS Employee, M.Name AS Manager
13FROM tblEmployee E
14LEFT JOIN tblEmployee M
15ON E.ManagerID = M.EmployeeID
1Self is joining a table to itself.
2
3-- assume employee table as 2 different table using different alias
4-- as manager and worker
5-- we want to join these 2 virtual manager and worker table
6-- to get manager's first name and worker's first name
7-- our condition is worker's manager_id match managers employee id
8
9SELECT manager.FIRST_NAME AS MANAGER_NAME ,
10 worker.FIRST_NAME AS WORKER_NAME
11FROM EMPLOYEES manager
12INNER JOIN EMPLOYEES worker on worker.MANAGER_ID = manager.EMPLOYEE_ID
13
14order by 1
15;