Re: Help: SQL .*Not IN* alternatives

Couldn't an OUTER Join be useful in this case? If the result of an OUTER join between idms and ncs had an ncs.ssn value of NULL then the idms ssn is not present in ncs. Malcolm Weallans OnLine Database Consultancy > I have a user with two flat files who is trying to find data in one > table that is not in the other. > The tables look like the following (most columns have been > omitted): > IDMS (6,000 rows) NCS (260,000 rows) > table table > -------------- ----------- > 333-33-3333 333-33-3333 > Anderson, Paul Anderson, Paul > > 444-44-4444 555-55-5555 > Smith, Ed Plicer, Felix > > 555-55-5555 777-77-7777 > Gordon, Samual Smith, John Q > > 666-66-6666 888-88-8888 > Powell, George Frederick, Emmett > > 888-88-8888 > Frederick, Emmett > > He wants a list of all ssn's in table IDMS and NOT IN table NCS. > Result set should be: > 444-44-4444 (plus other columns) > 666-66-6666 ( " ) > > 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.) > > Since this appears to be a common problem that we will run into > when converting data, I would be interested in hearing better > solutions. I seem to remember a previous discussion about this > here, but I've had no luck finding it. > > TIA, > Don Boothby (donald_boothby@ima.isd.state.in.us)