where is self join used code example

Example 1: 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 
;

Example 2: 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;