[SQL] TestDome Solution - Workers

문제

The following data definition defines an organization's employee hierarchy.

An employee is a manager if any other employee has their managerId set to this employee's id. That means John is a manager if at least one other employee has their managerId set to John's id.

TABLE employees
  id INTEGER NOT NULL PRIMARY KEY
  managerId INTEGER
  name VARCHAR(30) NOT NULL
  FOREIGN KEY (managerId) REFERENCES employees(id)

Write a query that selects only the names of employees who are not managers.

 

 

 

답안

3가지 방법으로 데이터를 추출할 수 있다.

 

    1. NOT IN

select name
from employees
where id not in (
                select a.id
                from employees as a
                inner join employees as b
                on a.id = b.managerId
                )

 

    2. LEFT JOIN & IS NULL

select a.name
from employees as a
left join employees as b 
on a.id = b.managerId
where b.name is null

 

    3. NOT EXISTS

select name
from employees as a
where not exists
                 (
                   select name
                   from employees as b
                   where a.id = b.managerId
                 )

 

 

• name 컬럼이 인덱스처리가 된 경우 성능차이는 없다.
• 만약, name 컬럼이 NOT NULL임을 보장하지 않는 경우 하위 쿼리 결과 집합에 NULL 값이 있는지 여부에 따라 다른 결과가 생성되므로
• NOT IN 대신 LEFT JOIN / IS NULL 또는 NOT EXISTS를 사용해야한다.