Re: Are These 2 Stateents Identical

Strictly speaking, the statements are not identical - meaning, they do not necessarily give the same result. The first statement will always delete correctly. The statement using IN will delete the same records as the one using the EXISTS subquery IF the SELECT statement used for the IN clause does not return rows containg all NULL. If it does, then the entire comparison of
IN(
  valueof1, valueof2, valueof3, valueof4   valueof1, valueof2, valueof3, valueof4   NULL, NULL, NULL, NULL)
will return FALSE, and nothing will be deleted.

Hth,
Radu

"Buck Turgidson" <jc_va_at_hotmail.com> wrote in message news:f98999c8.0110230941.3033cb43_at_posting.google.com...> I need to change some vendor code for tuning purposes. I was hoping
> that someone could verify that the 2nd statment is identical to the
> first?
>
> The 2nd runs much more quickly, but I just want to be safe.
>
> DELETE
> FROM CONTRACT
> WHERE EXISTS ( SELECT 'X'
> FROM CONTRACT_TEMP J
> WHERE J.CONTRACT_NUM = CONTRACT.CONTRACT_NUM
> AND J.CONTR_SEQ = CONTRACT.CONTR_SEQ
> AND J.CONTR_DT = CONTRACT.CONTR_DT
> AND J.CONTR_TYPE = CONTRACT.CONTR_TYPE );
>
>
>
> DELETE
> FROM CONTRACT
> WHERE (CONTRACT.CONTRACT_NUM,
> CONTRACT.CONTR_SEQ,
> CONTRACT.CONTR_DT,
> CONTRACT.CONTR_TYPE )
> IN ( SELECT J.CONTRACT_NUM,J.CONTR_SEQ,J.CONTR_DT,J.CONTR_TYPE
> FROM CONTRACT_TEMP J);
Received on Wed Oct 24 2001 - 05:29:49 CDT