self join in sql server
/* 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.EmployeeIDself join example
SELECT
e.first_name + ' ' + e.last_name employee,
m.first_name + ' ' + m.last_name manager
FROM
sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
manager;self join
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;self join in sql
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
;Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
