How to perform UNION of the tables with same column name as their primary keys but different values-CodePudding

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"]]}

CodePudding user response：

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