LEFT OUTER JOIN on multiple conditions
create table tableA (EX char(1), STATUS char(3), DLV varchar(10), Actual_FEE decimal(18,2));
insert into tableA values
('N', 'ACT', 'MAIL',   1.25),
('N', 'ACT', 'EMAIL',  1.25),
('N', 'ACT', 'PICKUP', 0),
('N', 'NON', 'MAIL',   0),
('N', 'NON', 'EMAIL',  1.25),
('N', 'NON', 'PICKUP', 0),
('Y', null,  null,     0);
GO



7 rows affected






create table tableB (EX char(1), STATUS char(3), DLV varchar(10), Expected_FEE decimal(18,2));
insert into tableB values
('N', 'ACT', 'MAIL',   1.25),
('N', 'ACT', 'EMAIL',  0),
('N', 'ACT', 'PICKUP', 0),
('N', 'NON', 'MAIL',   1.25),
('N', 'NON', 'EMAIL',  0),
('N', 'NON', 'PICKUP', 0),
('Y', null,  null,     0);
GO



7 rows affected



According to your expected result it could be:




select    a.EX, a.STATUS, a.DLV, a.Actual_FEE, b.Expected_FEE
from      tableA a
left join (select distinct EX, STATUS, DLV, Expected_Fee
           from   tableB) b
on        a.EX = b.EX
and       (a.STATUS = b.STATUS OR b.STATUS IS NULL)
and       (a.DLV = b.DLV or b.DLV IS NULL)
GO



EX | STATUS | DLV    | Actual_FEE | Expected_FEE
:- | :----- | :----- | :--------- | :-----------
N  | ACT    | MAIL   | 1.25       | 1.25        
N  | ACT    | EMAIL  | 1.25       | 0.00        
N  | ACT    | PICKUP | 0.00       | 0.00        
N  | NON    | MAIL   | 0.00       | 1.25        
N  | NON    | EMAIL  | 1.25       | 0.00        
N  | NON    | PICKUP | 0.00       | 0.00        
Y  | null   | null   | 0.00       | 0.00        



dbfiddle here