[코드카타] SQL 46 Primary Department for Each Employee

[코드카타] SQL 46 Primary Department for Each Employee

46. Primary Department for Each Employee
https://leetcode.com/problems/primary-department-for-each-employee/

Employees can belong to multiple departments. When the employee 
joins other departments, they need to decide which department is 
their primary department. Note that when an employee belongs to 
only one department, their primary column is 'N'.
Write a solution to report all the employees with their primary department. 
For employees who belong to one department, report their only department.
Return the result table in any order.

#union all을 활용한 방법 ( union 만 사용해도 결과값은 같음 )
select employee_id, department_id
from employee
group by employee_id
having count(department_id) = 1
union all
select employee_id, department_id
from employee
where primary_flag = 'Y'
group by employee_id

성급한 group by 활용으로 문제 해결이 막힘
조건은 상위 조건부터 하위 조건으로 하나씩 진행해야 쿼리적으로 좋은 결과를 얻을 수 있음
#union 없이 해결한 경우
select employee_id, department_id
from employee
where primary_flag = 'Y'
     or employee_id in 
     (select employee_id
     from employee
     group by employee_id
     having count(department_id) = 1)

• union vs union all
  • 중복값 제거 여부 ( union은 제거 / union all 은 포함 )