Re: Are These 2 Stateents Identical

In article <xKwB7.46$bw5.269_at_pollux.casema.net>, "Radu says...
>
>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

that is not true for IN and EXISTS. They are logically the same. Nulls do not affect their behaviour.

It is true for NOT IN and NOT EXISTS. NOT EXISTS is not a substitute for NOT IN and vice versa.

IN and EXISTS can be substituted for each other.

>
>
>"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);
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp