How to write a faster "NOT IN" Query With
"JOIN" ?

Greetings,

I have a select Query like this one:

Select *

from production_plan

where 

prod_cod NOT IN (

select distinct material_code from material_master

)

The problem is that the material_master is HUGE

and even using indexes the Query is slow.

I remeber once MS Access created for me a query with a JOIN

to do the same kind of query, but much faster.

I can't remember if it was a inner join or a outter join... 

And I don't remeber how to do that on Access anymore...

Please help

It won't necessarily be faster; it depends on indexes and optimization.

Select production_plan.*

from production_plan

left outer join  material_master on
production_plan.prod_cod=material_master.material_code

where 

material_code is null

You can also check out NOT EXISTS for speed.

Select *

from production_plan

where NOT EXISTS (select 1 FROM from material_master WHERE
material_code=prod_cod)
Remember that select * will be slower than selecting individual columns if you
have a lot of columns. Select only what you need if there is a lot of data in
the table row.

Thanks! It is 100 times faster!