How do i Select if not in subquery?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How do i Select if not in subquery?

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    46

    Angry

    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)
    ora_newbie@yahoo.com

  2. #2
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    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....

  3. #3
    Join Date
    Mar 2001
    Posts
    46

    Angry

    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.
    ora_newbie@yahoo.com

  4. #4
    Join Date
    Feb 2001
    Posts
    180
    How about this one:
    select AN_ID
    from
    AUTHOR_ZIPCODE
    minus
    select AN_ID
    from
    AUTHOR_ZIPCODE_DUPES

    Regards
    Ben de Boer

  5. #5
    Join Date
    Mar 2001
    Posts
    46

    Thumbs up

    Great!
    much more performant....this takes about 4 minutes on the table. I wish it would only take 2 minutes. Any more tips?
    ora_newbie@yahoo.com

  6. #6
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    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.





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width