合并具有不同列和数据的两个表 [英] Combine two tables with different columns and data

问题描述

即使表2上没有CompanyC的关系，如何合并两个具有不同数据的表并为表2中的所有数据设置CompanyC的值.

How can I combine two tables with different data and set value for CompanyC to all data in Table 2 even there is no relationship of CompanyC on Table2.

  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中没有数据.结果将为空.

I already tried this and it is working but the problem is since CompanyC has no data on Table 2. The result will be 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;

推荐答案

您可以对表进行INNER联接，并在ON子句中使用NOT EXISTS，如下所示:

You can do an INNER join of the tables and use NOT EXISTS in the ON clause like this:

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)

请参见演示.
结果:

See the demo.
Results:

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

这篇关于合并具有不同列和数据的两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！