How do i Select if not in subquery?

I have two views with an ID and an id_code field. (The underlying table is the same.) How can I select all the ID's from view 1 that are not present in view two?

The following code returns the ID's if it exists, but the opposite returns no data. I know that there is data that is in view 1 but not view 2.

select
AZ.AN_ID,
AZ.ZIP_CODE,
from
AUTHOR_ZIPCODE AZ
WHERE exists
(select AN_ID from
AUTHOR_ZIPCODE_DUPES AZD
)


(Please forgive double posting if it happens...I can't see the 1st post)

Hi ,

select
AZ.AN_ID,
AZ.ZIP_CODE,
from
AUTHOR_ZIPCODE AZ
WHERE not exists
(select AZD.AN_ID from
AUTHOR_ZIPCODE_DUPES AZD
where AZ.AN_ID=AZD.AN_ID)

Hope it works....

Is there another way? The performance is unacceptable for a small test table < 500 records. (I let it run for 20 minutes before killing it).

I can't imagine this on a real table with > 1 million records.

How about this one:
select AN_ID
from
AUTHOR_ZIPCODE
minus
select AN_ID
from
AUTHOR_ZIPCODE_DUPES

Great!
much more performant....this takes about 4 minutes on the table. I wish it would only take 2 minutes. :) Any more tips? ;)

from sql*plus:


WITH summary AS
(SELECT an_id FROM author_zipcode_dupes)
SELECT an_id FROM author_zipcode MINUS SELECT an_id FROM summary;



WITH clause increases performance.



F.