Oracle 21c

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

create table emp (empid, department) as
select 1, 'Retail' from dual union all
select 2, 'Retail' from dual union all
select 3, 'Corporate' from dual union all
select 4, 'Corporate' from dual union all
select 5, 'Corporate' from dual
5 rows affected

create table overhead (empid, overhead) as
select 1, 10 from dual union all
select 2, 20 from dual union all
select 3, 100 from dual union all
select 4, 120 from dual union all
select 5, 220 from dual
5 rows affected

SELECT e.EmpID, 
       e.Department, 
       o.Overhead as CorpOverhead
FROM   EMP e
       LEFT OUTER JOIN OVERHEAD o
       ON  e.EmpID      = o.EmpID
       AND e.Department = 'Corporate';


EMPID
DEPARTMENT
CORPOVERHEAD


3
Corporate
100

4
Corporate
120

5
Corporate
220

1
Retail
null

2
Retail
null


select e.EmpID, 
       e.Department, 
       o.Overhead as CorpOverhead
from   EMP e,
       OVERHEAD o
where  CASE e.department WHEN 'Corporate' THEN e.EmpID END = o.EmpID (+);


EMPID
DEPARTMENT
CORPOVERHEAD


3
Corporate
100

4
Corporate
120

5
Corporate
220

1
Retail
null

2
Retail
null

EMPID	DEPARTMENT	CORPOVERHEAD
3	Corporate	100
4	Corporate	120
5	Corporate	220
1	Retail	null
2	Retail	null