when are self joins used code example

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

Tags:

Misc Example