组合具有不同列和数据的两个表

Question

即使表2中没有CompanyC的关系，我如何将两个数据不同的表组合起来，并将CompanyC的值设置为表2中的所有数据。

Table 1:                                 Table 2:
company     jobs                          company     jobs     emp_name
-----------------------------------      ---------------------------------
CompanyA     IT                           CompanyA     IT        John
CompanyB     Business                     CompanyB     Business  Mike
CompanyC     Engineer                     

结果表如下所示：

company     jobs            emp_name           
------------------------------------------    
CompanyA     IT                John     
CompanyC     Engineer          John  
CompanyB     Business          Mike      
CompanyC     Engineer          Mike

我已经尝试过了，它正在工作，但问题是CompanyC在表2中没有数据。结果将为null。

select coalesce(t1.company, t2.company)
t1.jobs, t2.emp_name
from table1 t1 full outer join
table2 t2
on t2.name = t1.name;

Answer 1

您可以执行表的INNER联接，并在ON子句中使用NOT EXISTS，如下所示：

SELECT t1.company, t1.jobs, t2.emp_name
FROM Table1 t1 INNER JOIN Table2 t2
ON t2.company = t1.company
OR NOT EXISTS (SELECT 1 FROM Table2 WHERE company = t1.company)

请参阅演示
结果：

> company  | jobs     | emp_name
> :------- | :------- | :-------
> CompanyA | IT       | John    
> CompanyC | Engineer | John    
> CompanyB | Business | Mike    
> CompanyC | Engineer | Mike