sql - Combine two tables with different columns and data

Question

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                     

And the Result Table would be like:

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

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;

Answer 1

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