query on more tables

[raf]

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

[abhaysk]

select b.id from bx b where b.id not in ( select a.id from ax a )

[TomazZ]

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.

[abhaysk]

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.

[TomazZ]

Should I understand that when making a query you are not automatically thinking of performance?

[slimdave]

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