Re: exists clause

"Marcin Buchwald" <velvet_at_agora.pl> wrote in message news:<c50e39$l4l$1_at_pippin.warman.nask.pl>...
> Are the two conditions equivalent?
>
> *************************************************************
> and not exists (
> select 1 from plan_grzbietu p2, drzewo_mutacji d2
> where p2.mutgrb=d2.mutacja
> and p.dow=p2.dow and p.drw1_xx=p2.drw1_xx and d.poziom<d2.poziom
> and p.mutgrb in (
> select mutacja from drzewo_mutacji d3
> start with mutacja=d2.mutacja connect by prior root_xx=xx
> )
> )
> **************************************************************
> and (
> select max(p2.mutgrb) from plan_grzbietu p2, drzewo_mutacji d2
> where p2.mutgrb=d2.mutacja
> and p.dow=p2.dow and p.drw1_xx=p2.drw1_xx and d.poziom<d2.poziom
> and p.mutgrb in (
> select mutacja from drzewo_mutacji d3
> start with mutacja=d2.mutacja connect by prior root_xx=xx
> )
> ) is null
> **************************************************************
>
> on ORA9203EENT they are not in my specific query.
>
> Could someone explain it to me?
>
> Regards,
>
> Marcin

One item I noticed, is p2.mutgrb a nullable field? If so, you results sets will be spurious. A Max(of a null) will always be null or return a spurious value, you may need to nvl the column before doing the max, i.e. max(nvl(p2.mutgrb,0). Without diving too deep into it, your two conditions will always be different, i.e. in the second you're looking for a record(s) that has p2.mutgrb that's null. In the first you're looking for a record(s) that don't exist in p2 & d2. One last item, in your where condition "and p.mutgrb in (", you may see better performance if you change that to an 'exists' clause.

HTH,
Pete's Received on Wed Apr 07 2004 - 08:25:27 CDT