验证筛选UM_SERVICE/LOS表diag_code是否匹配查找表的SQL正确性

当前业务场景下diag_code仅存储在UM_SERVICE或LOS表中，暂无法通过同时关联两张表获取统一非空的diag_code。存在LOS和UM_SERVICE的不同行同时存在diag_code且取值不同的情况，二者中任意一个或两个都可能在TABLE_LOOKUP查找表中匹配到对应记录。
现需要判断以下写法是否可以正确查询出diag_code存在于两张表之一且匹配查找表的对应记录。

SELECT distinct
      c.id
      ,uc.id
      ,c.person_id 
    FROM dbo.CASES c 
    INNER JOIN dbo.UM_CASE uc with (NOLOCK) ON uc.case_id = c.id 
    LEFT JOIN dbo.UM_SERVICE sv (NOLOCK) ON sv.case_id =  omc.case_id 
    LEFT JOIN dbo.UM_SERVICE_CERT usc on usc.service_id = sv.id  
    LEFT JOIN dbo.LOS S WITH (NOLOCK) ON S.case_id = UC.case_id 
    LEFT JOIN dbo.LOS_EXTENSION SC WITH (NOLOCK) ON SC.los_id = S.id 
    INNER JOIN dbo.PERSON op with (NOLOCK) on op.id = c.Person_id
    WHERE
        (sv.diag_code is not null and c.case_id = sv.case_id
        or
        s.diag_code is not null and c.case_id = s.case_id)
      and     
        (sv.diag_code is not null and sv.diag_code in (select diag_code from TABLE_LOOKUP)
        or
        s.diag_code is not null and s.diag_code in (select diag_code from TABLE_LOOKUP)

CASES
id   person_id   

UM_CASE
case_id  

LOS
case_id  id

LOS_EXTENSION
los_id

Person
id    cid

UM_SERVICE
case_id     diag_code

UM_SERVICE_CERT
service_id    id

TABLE_LOOKUP
diag_code

原有写法存在多处问题，无法直接正确执行，修正后可满足需求但冗余度高，具体问题如下：

• 语法错误：关联UM_SERVICE的ON条件中使用了未定义的别名omc，应改为uc.case_id；同时WHERE条件末尾缺失右括号，直接执行会报错
• 逻辑冗余：左关联UM_SERVICE和LOS时已经通过case_id完成关联校验，WHERE条件中c.case_id = sv.case_id和c.case_id = s.case_id的判断完全多余；另外IN判断对NULL值会自动返回不匹配，不需要额外加is not null的判断
• 性能隐患：查询中关联了UM_SERVICE_CERT和LOS_EXTENSION两张表，但没有用到这两张表的字段，也没有相关过滤条件。如果这两张表和关联表是一对多关系，会导致结果行意外膨胀，额外增加DISTINCT的去重开销，无业务必要的前提下可以删除这两个LEFT JOIN

SELECT DISTINCT
      c.id
      ,uc.id
      ,c.person_id 
FROM dbo.CASES c 
INNER JOIN dbo.UM_CASE uc WITH (NOLOCK) ON uc.case_id = c.id 
LEFT JOIN dbo.UM_SERVICE sv (NOLOCK) ON sv.case_id =  uc.case_id 
LEFT JOIN dbo.LOS S WITH (NOLOCK) ON S.case_id = uc.case_id 
INNER JOIN dbo.PERSON op WITH (NOLOCK) ON op.id = c.Person_id
WHERE
    sv.diag_code IN (SELECT diag_code FROM TABLE_LOOKUP)
    OR s.diag_code IN (SELECT diag_code FROM TABLE_LOOKUP)

如果业务必须保留UM_SERVICE_CERT和LOS_EXTENSION的关联，可以自行加回对应LEFT JOIN语句

内容的提问来源于stack exchange，提问作者Michele