Oracle filtering with multiple Exists but only one can evaluate

Please consider this pseudo-query:

SELECT *
FROM A
WHERE 
   EXISTS (SELECT 1 FROM B WHERE B.ID = :ID)
   OR EXISTS (SELECT 1 FROM C WHERE C.NAME = :NAME)

Consider that:

• “From A” is much more complex and I don’t want to do more joins there.
• I’m filtering results and foreach record A i just want to return a single row, that’s why I have the exists in the order i want them to evaluate;

How can I avoid running the 2nd exists (due to performance) if the first one already evaluates “true”?

Thanks

Answer

You could use a case expression:

SELECT *
FROM A
WHERE 
    1 = CASE
        WHEN EXISTS (SELECT 1 FROM B WHERE B.ID = :ID) THEN 1
        WHEN EXISTS (SELECT 1 FROM C WHERE C.NAME = :NAME) THEN 1
    END

This takes advantage of the short-circuit evaluation behavior of case, which is described in the documentation:

For a searched CASE expression, the database evaluates each condition to determine whether it is true, and never evaluates a condition if the previous condition was true.