-
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
-
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"
-
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
-
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"
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|