SQL Knowledge: When FULL OUTER JOIN
isn't Working, Try UNION
- Firstly I tried
FULL OUTER JOIN
but it didn't work and got an ERROR. - Then I found
union
can replace theFULL OUTER JOIN
. So I did.
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
.
# 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