尝试基于具有IN连接的四个字段来查询优化连接

I have this query:

我有这个问题：

SELECT 
      RTPropertyUniqueIdentifier
      ,[FA Unique Listing Identifier - Ref ID]
      ,MLS.[Listing Tracking ID]
      , MLS.[Assessor's Parcel Identification Number] as MLS_PARCELID
      , PP.ParcelID
      , GEOID
      , CASE 
        WHEN PP.PP_ParcelID = MLS.MLS_PARCELID then 'PARCEL MATCH'
        Else 'ADDRESS MATCH' END as MATCHTYPE
      ,[Update Timestamp]
          INTO PROPERTY.DBO.PP_MLS_Bridge
  FROM STAGE.DBO.STAGE_MLS_BRG MLS
  join STAGE.DBO.STAGE_PP_BRG PP on 
    (
        PP.PP_ParcelID = MLS.MLS_PARCELID
    ) OR (
        lower(MLS.StreetNum) = lower(PP.AddNum)
        and (lower(PP.AddStreet) like '%'+lower(MLS.Street_Name)+'%' or lower(MLS.Street_Name) like CONCAT('%', lower(PP.AddStreet), '%'))
        and (lower(PP.AddUnitNum) like '%' + lower(MLS.Unit) + '%' or lower(MLS.Unit) like CONCAT('%', lower(PP.AddUnitNum), '%'))
        and lower(PP.AddCity) = lower(MLS.[Property City])
        and lower(PP.AddState) = lower(MLS.[Property State])
        and lower(PP.AddZip) = lower(MLS.[Property Zip])
);
SELECT 
      RT