finding records in one table but not the other

[user2222]

I am writing some queries which are designed to return rows from one table which have no matching records in the other.

The following takes around half a minute or so and is the best solution I've come up with so far:

select a.orderid,a.part,a.qty,b.partflag from
(select orderid,part,qty from tblorder1 where qty > 0
and not exists
( select id from tblorder2 where qty > 0
and orderid = id
)a,
(select part,partflag from iteminfo)b
where a.part = b.part

The 2 main subsections return around 600-700 records each.

Is there any speedier way of obtaining records in the first table but not the other ?
I've tried outer join format and that's even slower.

[DaPi]

Is that any quicker than the simple solution (approx):select a.orderid, a.part, a.qty, b.partflag
from tblorder1 a, iteminfo b
where a.part = b.part
and a.qty > 0
and not exists
(select id from tblorder2 c
where c.qty > 0
and a.orderid = c.id)

[Cookies]

sure would be easier if you used table aliases everywhere.
anyways ...

1. What indexes do you have on the tables?
2. What is the existing PLAN for the query?
3. is tblorder2.id a NOT NULL field?
4. If orderid = id can you have a tblorder2.qty > 0 but the tblorder1.qty = 0?

without this knowledge my first pass is:

select a.orderid, a.part, a.qty, b.partflag
from
(select orderid, part, qty
from tblorder1 t1
where orderid not in (select id from tblorder2 where qty > 0)
and qty > 0) a,
iteminfo b
where a.part = b.part

knowing what columns are the PKs and indexes would totally change
the possible writing of the query.

[oracle_faq]

Another approach would be using minus

select col1 alias1, col2 alias2
from table1
where something = nothing
minus
select col3 alias1, col2 alias2
form table2
where something = everything

This may be faster in certain cases because you are not doing a lookup in second table for every record in the first table especially when your where clause may have unindexed criteria only.