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
;