Exist

Transact-SQL
1

is it right to use the exist as follows?

WHERE EXISTS (SELECT 1
FROM PAT_ENC x1
WHERE X1.PAT_ID = P.PAT_ID
AND x1.SERV_AREA_ID in ('10','11')
AND x1.APPT_STATUS_C in (1,2,6)
AND ROWNUM = 1 )
OR
EXISTS(
SELECT 1
FROM CLARITY_TDL_TRAN
WHERE CLARITY_TDL_TRAN.PAT_ID = p.PAT_ID
AND CLARITY_TDL_TRAN.SERV_AREA_ID IN ('10','11')
AND CLARITY_TDL_TRAN.DETAIL_TYPE = 1
AND CLARITY_TDL_TRAN.DETAIL_TYPE <> 10
AND ROWNUM = 1
)

2

"right" depends on your context. note that you should be able to write

where exists 
(
    select ...
    union all 
    select ...
)

A bit shorter

1 Like
3

I would never do UNION ALL here, as I think the optimizer can much better optimize the separate EXISTS.