Cross-checking Data Between 2 Tables 1

[TimboA]

I have a table for customers (containing approx 45,000 records) from which I am extracting Account No., Name and Postcode as follows :-

Select a.account_no
,a.name
,a.postcode
from customers a

I have another table which contains valid UK postcodes (approx 1.6M records).

I have been asked to find out which customer records are holding invalid postcodes.

I've tried :-

Select a.account_no
,a.name
,a.postcode
from customers a
where a.postcode not in (select b.postcode from postcode_tab b)

But this query runs and runs and runs and runs.....

I've also tried using Indexes on both tables, but the NOT IN clause by-passes the Indexes and does full table scans.

Anyone got any SQL advise on how to do this query ??

Thanks.

 

[SpiritOfLennon]

Try

Select a.account_no
,a.name
,a.postcode
from customers a, postcode_tab b
where b.postcode is null
SOL
Yeah people they won't understand,
Girlfriends they don't understand,
In spaceships they don't even understand,
and me I aint ever gonna understand

 

[SpiritOfLennon]

Sorry that should of been

Try

Select a.account_no
,a.name
,a.postcode
from customers a, postcode_tab b
where a.postcode(+)= b.postcode
and b.postcode is null
I think SOL
Yeah people they won't understand,
Girlfriends they don't understand,
In spaceships they don't even understand,
and me I aint ever gonna understand

 

[Kenrae]

NOT IN clause is reaaaally slow on Oracle. You can try NOT EXISTS:

Select a.account_no
,a.name
,a.postcode
from customers a
where not exists (select '1' from postcode_tab b
                  where a.postcode=b.postcode);

Of course, you should have indexes on both tables (on column postcode).

Another approach is to use minus clause:

Select a.account_no
,a.name
,a.postcode
from customers a
where exists (select '1' from
 (select postcode from customers
  minus
  select postcode from postcode_tab)
 where a.postcode=postcode);

You need ordered indexs on both tables if you want this query to end sometime this year.

You can even load temporal data to a temp table, and cross table customer with this temp table. Something like:

create table temp nologging as(
select postcode from customers
minus
select postcode from postcode_tab);

create (unique?) index tmp on temp(postcode);

Select a.account_no
,a.name
,a.postcode
from customers a
where exists (select '1' from temp
              where postcode=a.postcode);