Self join Example

Display the hierarchy of all employees with rank

Create employee table

create table emp(
        id int,
                    fname varchar(16),
                    lname varchar(16),
                    e_level int
);

Insert data

joins.xlsx

See all rows

select * from emp

all employees with rank as 1

select id, fname+' '+lname as FullName, 1 as EmpRank
from emp

All employees with no senior

select id, fname+' '+lname as FullName, 1 as EmpRank 
from emp where e_level is NULL

self join

SELECT distinct E1.id,E1.fname+' '+E1.lname as FullName,  E2.e_level + 1 as EMPRANK 
  FROM emp E1 inner JOIN emp E2 ON E1.e_level = E2.e_level

now final query

select id, fname+' '+lname as FullName, 1 as EmpRank
from emp where e_level is NULL
 UNION 
SELECT distinct E1.id,E1.fname+' '+E1.lname as FullName,  E2.e_level + 1 as EMPRANK 
FROM emp E1 inner JOIN emp E2 ON E1.e_level = E2.e_level

Same without self join

-- Top-level employees (no e_level → Rank 1)
SELECT 
    id, 
    fname + ' ' + lname AS FullName, 
    1 AS EmpRank
FROM emp
WHERE e_level IS NULL

UNION

-- Other employees (rank = e_level + 1)
SELECT 
    id, 
    fname + ' ' + lname AS FullName, 
    e_level + 1 AS EmpRank
FROM emp
WHERE e_level IS NOT NULL;