How to make conditional join when containing Nulls in some columns use for join Is there a way to make the following join more efficient? It needs to join the following:


Always on dset_cob_dt and tlsn_trd_id 
In addition if the meas_data.tlsn_leg_id is not null it needs to join on tlsn_leg_id as well 
and in addition to that also on tlsn_vrsn_num if similar to the last one meas_data.tlsn_vrsn_num is not null.


The below query seems to be incorrect. What would be the correct way to do it?

Select...
     left outer join tlea
      on (dset_cob_dt = tlea_cob_dt
          and
          ((meas_data.tlsn_trd_id = tlea.tlsn_trd_id
          and meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num
          and meas_data.tlsn_leg_id = tlea.tlsn_leg_id)
          or (meas_data.tlsn_trd_id = tlea.tlsn_trd_id
          and meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num)
          or (meas_data.tlsn_trd_id = tlea.tlsn_trd_id))
          )


One possibility would be the use of nvl in oracle. But I'm not sure how I can achieve the same result as the above. It is the data on the meas_data side which sometimes contains Null for tlsn_vrsn_num and/or tlsn_leg_id, in which case it should ignore those columns for the join.
First of all, your description does not match the ON condition. Your description would fit an ON like this:

Select ...
from ...
     meas_data  m
     left outer join  tlea  t
       on  m.dset_cob_dt = t.tlea_cob_dt
       and m.tlsn_trd_id = t.tlsn_trd_id
       and (
                 m.tlsn_vrsn_num = t.tlsn_vrsn_num
             and m.tlsn_leg_id   = t.tlsn_leg_id
           or    
                 m.tlsn_vrsn_num = t.tlsn_vrsn_num
             and m.tlsn_leg_id is null
           or                                          
                 m.tlsn_vrsn_num is null
           )


As for efficiency, I'd suggest a composite index on the 4 columns, on each table (but I'm nowhere near a Oracle expert, so take this suggestion with caution).