멘토링 짝궁 리스트 : solvesql

-- 매칭 가능한 경우 모두 포함 

WITH tee AS (
  SELECT  employee_id as mentee_id, name as mentee_name, department
  FROM    employees
  WHERE   join_date <= '2021-12-31' AND
          join_date >= '2021-09-30'
),
    tor AS (
  SELECT  employee_id as mentor_id, name as mentor_name, department
  FROM    employees
  WHERE   join_date <= '2019-12-31'
)
SELECT  tee.mentee_id, tee.mentee_name, tor.mentor_id, tor.mentor_name
FROM    tee LEFT JOIN tor ON tee.department <> tor.department
UNION
SELECT  tee.mentee_id, tee.mentee_name, tor.mentor_id, tor.mentor_name
FROM    tee RIGHT JOIN tor ON tee.department <> tor.department
ORDER BY  
        mentee_id, mentor_id