Finding Duplicates in large lists

[lukemcc]

I have a table of 75,000 username's that I need to match to another table that contains 2.5 million usernames.

Other than a simple left outer join of my 75K table to the master table I am stumped on the best way to do this.

I am on db2 9.1 and the below query has been running for a little over 4 hours. Is there a better/faster way?

select w.conuser, w.email
from(
SELECT
w.conuser,
w.email,
c.masterconFK
FROM
washingtoncon05 w
left outer join mastercontacts c on w.conuser = c.masterconFK )a
where c.masterconFK is null


Thanks!

 

[fredericofonseca]

try
select w.conuser
,w.email
FROM washingtoncon05 w
where not exists
(select 1
from mastercontacts c
where w.conuser = c.masterconFK
)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[lukemcc]

Thanks Frederico,

I added an index to the tables, did a runstats and then used your query. Finished running in just a few minutes.

Thanks,
Luke