Re: Help: SQL .*Not IN* alternatives
In article <39dp75$d17@emory.mathcs.emory.edu>
DONALD_BOOTHBY@IMA.ISD.STATE.IN.US writes:
>
> He used the following SQL:
> SELECT DISTINCT "idms"."ssn", "idms"."lastname", "idms"."firstname",
> "idms"."mi", "idms"."ssaciid", "idms"."college", "idms"."initials"
> FROM "idms", "ncs"
> WHERE ( "idms"."ssn" NOT IN (SELECT DISTINCT "ncs"."ssn" from
> "ncs"))
> ORDER BY "idms"."college" ASC>
> but since these files are large, it was still chugging along 4 hours
> after the query was started. One reason why it was taking so long was
> mainly due to the query running in a local version of WATCOM's SQL
> database. (Note: these tables will eventually find their way into an
> Informix database just as soon as I can get all the pieces/parts to
> talk to each other.)
One problem is that you are using the two tables and not joining them
in the first select :
Try
SELECT DISTINCT "idms"."ssn", "idms"."lastname", "idms"."firstname",
"idms"."mi", "idms"."ssaciid", "idms"."college", "idms"."initials"
FROM "idms"
WHERE ( "idms"."ssn" NOT IN (SELECT DISTINCT "ncs"."ssn" from
"ncs"))
ORDER BY "idms"."college" ASC
A better way to do this might be
select idms.*,ncs.ssn ncs_ssn from idms,outer ncs
where ncs.ssn=idms.ssn
into temp t1;
select distinct ssn,lastname,firstname,mi,ssaciid,college,initials
from t1
where ncs_ssn is null
Mike Aubury