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

Try using:

SELECT QUOTE.REFER_NUMB FROM QUOTE Q
MINUS
SELECT XREF.XREFER_NUMB FROM XREF You will find the performance will be much better.

Bob Morrison Received on Wed Sep 18 1996 - 00:00:00 CEST