query on more tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: query on more tables

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    query on more tables

    Hi,
    I've 2 tables:
    TABLE AX:
    ID NUMBER PK

    TABLE BX:
    ID NUMBER FK

    TABLE A HAS 2000 RECORDS
    TABLE B HAS 3500 RECORDS

    I'd like to write a query to find 1500 record not equal.

    I tried this:
    select a.id, b.id
    from ax a, bx b
    where a.id <> b.id
    group by a.id, b.id


    but It not run correctly

    How can I write this query??

    Thanks
    Raf

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    select b.id from bx b where b.id not in ( select a.id from ax a )
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by abhaysk
    select b.id from bx b where b.id not in ( select a.id from ax a )
    Logically correct, but
    select b.id from bx b where not exists (select * from ax a where a.id = b.id)
    or simple
    select id from bx
    minus
    select id from ax
    would be faster.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by TomazZ
    Logically correct, but
    select b.id from bx b where not exists (select * from ax a where a.id = b.id)
    Even better for this would be

    select b.id from bx b where not exists (select 'X' from ax a where a.id = b.id)

    But, you know what, there will be N Solution for 1 Question....and i dont want to list all...Its upto the thread starter to dig something from NET or Manuals to find efficient way.

    Of course, had he asked for efficient and logically corect ans, then I would bet the above query would out perform rest.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Should I understand that when making a query you are not automatically thinking of performance?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Ouch!

    Personally i like the MINUS solution -- the query says exactly what you want to do. The correlated subquery would be faster, i would think, where "a" has very few rows and "b" has very many.

    The answer of course is to benchmark the performance of each methodology for your own data set
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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