Application Design and Tuning for Performance › Search Strategies › Name Searching › During Join Predicate

During Join Predicate

The following query uses a during join predicate to probe on the first condition and to apply the second condition only if the first does not find anything. That is, it will execute the second search only if the first finds nothing and completely avoid the second probe into the table. This query may not produce the same results as other queries:

SELECT COALESCE(A.PERSON_ID, B.PERSON_ID)
FROM	  SYSIBM.SYSDUMMY1
LEFT OUTER JOIN
       PERSON_TBL A
ON IBMREQD = 'Y'
AND  (A.LASTNAME = 'RADY' OR A.LASTNAME IS NULL)
AND  (A.FIRST_NAME = 'BOB' OR A.FIRST_NAME IS NULL)
LEFT OUTER JOIN
(SELECT PERSON_ID
 FROM   PERSON_TBL
 WHERE  LASTNAME = 'BOB'  AND FIRSTNME = 'RADY') AS B
ON A.EMPNO IS NULL