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([emp_id] int, [company] varchar(8), [jobs] varchar(8), [emp_name] varchar(5));
    
INSERT INTO Table2([emp_id], [company], [jobs], [emp_name]) VALUES
    (1, 'CompanyA', 'IT', 'John'),
    (2, 'CompanyB', 'Business', 'Mike'),
    (3, 'CompanyD', 'Nurse', 'Mitch');
3 rows affected

SELECT t2.emp_id, 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)
UNION ALL
SELECT t2.*
FROM Table2 t2
WHERE NOT EXISTS (SELECT 1 FROM Table1 WHERE company = t2.company)


emp_id
company
jobs
emp_name


1
CompanyA
IT
John

1
CompanyC
Engineer
John

2
CompanyB
Business
Mike

2
CompanyC
Engineer
Mike

3
CompanyC
Engineer
Mitch

3
CompanyD
Nurse
Mitch


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


emp_id
company
jobs
emp_name


1
CompanyA
IT
John

1
CompanyC
Engineer
John

2
CompanyB
Business
Mike

2
CompanyC
Engineer
Mike

3
CompanyC
Engineer
Mitch

3
CompanyD
Nurse
Mitch

emp_id	company	jobs	emp_name
1	CompanyA	IT	John
1	CompanyC	Engineer	John
2	CompanyB	Business	Mike
2	CompanyC	Engineer	Mike
3	CompanyC	Engineer	Mitch
3	CompanyD	Nurse	Mitch