Answers for "self join in sql server"

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;

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 
;