1965. Employees With Missing Information.md

1965. Employees With Missing Information

Question Link

SQL Knowledge: When FULL OUTER JOIN isn't Working, Try UNION

Intuition

• Firstly I tried FULL OUTER JOIN but it didn't work and got an ERROR.
• Then I found union can replace the FULL OUTER JOIN. So I did.

The union Part Code

select e.employee_id, e.name, s.salary from Employees e left join Salaries s on e.employee_id=s.employee_id group by employee_id
union
select s.employee_id, e.name, s.salary from Salaries s left join Employees e on e.employee_id=s.employee_id group by employee_id

The result of this code is:

| employee_id | name     | salary |
| ----------- | -------- | ------ |
| 2           | Crew     | null   |
| 4           | Haven    | 63539  |
| 5           | Kristian | 76071  |
| 1           | null     | 22517  |

It looks just like FULL OUTER JOIN. I decide to use union.

Code

# Write your MySQL query statement below
select a.employee_id from (
  select e.employee_id, e.name, s.salary from Employees e left join Salaries s on e.employee_id=s.employee_id group by employee_id
  union
  select s.employee_id, e.name, s.salary from Salaries s left join Employees e on e.employee_id=s.employee_id group by employee_id
) a
where a.salary is null or a.name is null
order by a.employee_id asc