These are the two tables: Input:
Employees table:
+-------------+----------+
| employee_id | name |
+-------------+----------+
| 2 | Crew |
| 4 | Haven |
| 5 | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5 | 76071 |
| 1 | 22517 |
| 4 | 63539 |
+-------------+--------+
I want an output like how you perform join but using the union.
The output of the union should look like this:
employee_id | name | salary
2 crew. null
4. haven. 63539
5. Kristian 76071
1. null. 22517
and post doing the union I want to perform select, by selecting the employee_id on employees with either no name or no salary
Query currently I am working on :
select * from
(select employee_id, name, null as salary from employees
union all
select employee_id, null as name, salary from salaries)
as emp
where name is null or salary is null
The result looks like this:
{"headers": ["employee_id", "name", "salary"], "values": [[2, "Crew", null], [4, "Haven", null], [5, "Kristian", null], [5, null, 76071], [1, null, 22517], [4, null, 63539]]}
if use this query:
select * from (select employee_id, name from employees union all select employee_id, salary from salaries) as emp
the result looks like this:
{"headers": ["employee_id", "name"], "values": [[2, "Crew"], [4, "Haven"], [5, "Kristian"], [5, "76071"], [1, "22517"], [4, "63539"]]}
This is a full join:
Select coalesce(e.employeeid, s.employeeid) employed
e.name, e.salary
From employees e full join
Salaries s
On e.employeeid = s.employeeid