Re: SQL-question

Doctor Stan wrote:
>
> Can anybody help me? My task is the following -
> I need to select all reference numbers from table QUOTE
> which are not present in table XREF.
> I need the most effective variant.
>
> Example:
>
> QUOTE
> 1001
> 1002
> 1003
>
> XREF
> 1001
> 1004
>
> ->result must be:
>
> 1002
> 1003
>
> I have writte 4 statements and I don't know which one is best for
> perfomance.
>
> 1. SELECT QUOTE.REFER_NUMB
> FROM QUOTE Q
> WHERE
> Q.REFER_NUMB NOT IN
> (SELECT XREF.XREFER_NUMB FROM XREF)
>
> 2. SELECT QUOTE.REFER_NUMB
> FROM QUOTE Q
> WHERE NOT EXIST
> (SELECT XREF.XREFER_NUMB
> FROM XREF
> WHERE
> Q.REFER_NUMB = XREF.XREFER_NUMB)
>
> 3. SELECT QUOTE.REFER_NUMB
> FROM QUOTE Q
> WHERE
> (SELECT COUNT(XREF.XREFER_NUMB)
> FROM XREF
> WHERE
> Q.REFER_NUMB = XREF.XREFER_NUMB) = 0
>
> 4. SELECT QUOTE.REFER_NUMB
> FROM QUOTE Q
> WHERE
> Q.REFER_NUMB <> ANY
> (SELECT XREF.XREFER_NUMB
> FROM XREF
> WHERE
> Q.REFER_NUMB = XREF.XREFER_NUMB) = 0
>
> Please, write the answer to my personal address
> Thanks!
>
> Stan
Consider this one as well:
SELECT REFER_NUMB FROM QUOTE
MINUS
SELECT XREFER_NUMB FROM XREF;
especially for parallel quiery option.

Eugene.

-- 
********************************************************
*        Everything above is only my opinion           *
********************************************************
*						       *
* If you see a lion in a cage and sign says "elephant" * 
*                      ,                               *
*           DO not belive your eyes !                  *
*       (Kozma Prutkov. (informal translation))        *
*                                                      *
********************************************************