Regarding query efficiency

sathish_rathinam · New User Joined: 22 Aug 2005 Posts: 59 Location: india

Hi ,

I want to know which one of the two queries written below is performance efficient

1.SELECT DISTINCT A . CUST_ID_NUMBER

FROM TEJAPCL_AP_CT_LK A

WHERE A . CUST_ID_NUMBER NOT IN (

SELECT A . CUST_ID_NUMBER

FROM TEJAPCL_AP_CT_LK A , TEJAPTM_AP_TR_MSTR B

WHERE B . APP_REF_NUMBER = A . APP_REF_NUMBER GROUP BY

CUST_ID_NUMBER )

2.SELECT DISTINCT C . CUST_ID_NUMBER

FROM TEJAPCL_AP_CT_LK C

WHERE NOT EXISTS (

SELECT A . CUST_ID_NUMBER

FROM TEJAPCL_AP_CT_LK A , TEJAPTM_AP_TR_MSTR B

WHERE B . APP_REF_NUMBER = A . APP_REF_NUMBER

AND C.CUST_ID_NUMBER = A.CUST_ID_NUMBER )

Regards,
Sathish

dbzTHEdinosauer · Posted: Wed Jul 30, 2008 12:48 pm

since you obviously ran an explain, what did the explain indicate?

ashimer · Posted: Wed Jul 30, 2008 2:14 pm

sudhakar84 · New User Joined: 20 Jun 2008 Posts: 25 Location: chennai

Hi,

If the result of the outer query is small and the result set of the inner sub-query is large, then use EXISTS - if it is the other way around, then use the IN clause.