I am trying to join 2 tables on 4 different columns. However, one of these columns is sometimes blank and is causing an unexpected output in the final table.

I have the following dataset:

CREATE TABLE #df 
(
    CityID bigint,
    StateID VARCHAR (5),
    HospitalID bigint,
    ProviderID BIGINT,
    PT_Count BIGINT 
);

INSERT INTO #df (CityID, StateID, HospitalID, ProviderID, PT_Count)
VALUES
(70289, 'WY', 10001, 34523, 300),
(70500, 'AZ', 15000, NULL ,200),
(34523, 'NM', 54634, 78543, 100),
(90010, 'CA', 65738, NULL, 500)

and would like to join it to this dataset

CREATE TABLE #df2 
(
    CityID bigint,
    City_Name VARCHAR(20),
    StateID VARCHAR (5),
    State_Name VARCHAR(20),
    HospitalID bigint,
    Hospital_Name VARCHAR(20),
    ProviderID BIGINT,
    Provider_Name VARCHAR(20)
);

INSERT INTO #df2 (CityID, City_Name, StateID, State_Name, HospitalID, Hospital_Name, ProviderID, Provider_Name)
VALUES
(70289, 'Cheyenne', 'WY', 'Wyoming', '10001', 'Wyoming Gen', 34523, 'Dr.Joe'),
(70500, 'Phoenix', 'AZ', 'Arizona', 15000, 'Arizona Gen', NULL, NULL),
(34523, 'Santa Fe', 'NM', 'New Mexico', '54634', 'NM Gen',78543, 'Dr. Jim'),
(90010, 'Beverly Hills', 'CA', 'California', 65738, 'Bev Gen', NULL, NULL)

When I run the following query, the records with null provider ids have null record for CityName, StateName, and HospitalName. This should not be the case.

How can I fix this?

SELECT a.CityID
     , b.City_Name
     , a.StateID
     , b.State_Name
     , a.HospitalID
     , b.Hospital_Name
     , a.ProviderID
     , b.Provider_Name
     , a.PT_Count
FROM #df a
LEFT JOIN #df2 b ON b.CityID = a.CityID 
                 AND b.HospitalID = a.HospitalID 
                 AND b.ProviderID = a.ProviderID 
                 AND b.StateID = a.StateID