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.EmployeeID
self 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;
what is 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
