SQL-question

From: Doctor Stan <76305.324_at_compuserve.com>
Date: 1996/09/17
Message-ID: <323F1869.68CA_at_compuserve.com>#1/1

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 Received on Tue Sep 17 1996 - 00:00:00 CEST