SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Table1([company] varchar(8), [jobs] varchar(8));
    
INSERT INTO Table1([company], [jobs]) VALUES
    ('CompanyA', 'IT'),
    ('CompanyB', 'Business'),
    ('CompanyC', 'Engineer');
3 rows affected

CREATE TABLE Table2([company] varchar(8), [emp_name] varchar(4));
    
INSERT INTO Table2([company], [emp_name]) VALUES
    ('CompanyA', 'John'),
    ('CompanyB', 'Mike');
2 rows affected

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

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