Re: NOT IN vs MINUS

Not quite -
The effect of the SET operators is also to eliminate duplicates.

If there is no overlap as in your example, then the effect is notionally equivalent to

    select distinct * from FOO;

The NOT IN variant leaves duplicates in the result set.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Walter Dnes wrote in message ...
>On Thu, 10 Feb 2000 18:50:29 +1600,
>wasef <mazafar4NOmaSPAM_at_onebox.com.invalid>
>wrote in article <1d3767a6.8cc3a038_at_usw-ex0108-062.remarq.com>:
>
>> What is the difference betweein NOT IN and MINUS function?
>> As Informix is not having MINUS function, then what will be
>> impact on the query?
> MINUS is a set operator (remember Venn diagrams?)
>
> SELECT * FROM FOO
> MINUS
> SELECT * FROM BAR;
>
> returns records that meet two conditions
> 1) They are part of SELECT * FROM FOO
> AND
> 2) They are not in SELECT * FROM BAR
>
> If you understand Venn diagrams, draw two circles that
>overlap slightly.
> Circle A = SELECT * FROM FOO;
> Circle B = SELECT * FROM BAR;
> The MINUS operation above returns that portion of Circle A
>which is NOT overlapped by Circle B. If there is no overlap
>then the the MINUS query is equivalent to SELECT * FROM FOO;
>
>--
>Walter Dnes <waltdnes@waltdnes.org> http://www.waltdnes.org
>SpamDunk Project procmail spamfilters.
>A picture is worth a thousand words; unfortunately,
>it consumes the bandwidth of ten thousand words.
Received on Sun Feb 13 2000 - 02:42:43 CST