Thread: RE: [SQL] finding rows in one table not in another

> I need to find the set of rows in one table that are not in another
> table.  I used a query like the following:
>
>     select distinct id from mytable1 where id not in (select
> distinct id from mytable2);
>
> However, this revealed no rows, even though I know of instances where
> id from mytable1 is absent from id in mytable2.  Is there some other
> way to do this?  Am I missing something?  Is there a workaround?
>
> Cheers,
> Brook
>
try:
    SELECT mytable1.id
     FROM mytable1
    WHERE NOT EXIST (SELECT mytable2.id
                       FROM mytable2
                      WHERE mytable1.id = mytable2.id);
        -DEJ

   > I need to find the set of rows in one table that are not in another
   > table.  I used a query like the following:
   >
   >     select distinct id from mytable1 where id not in (select
   > distinct id from mytable2);
   >
   try:
       SELECT mytable1.id
    FROM mytable1
       WHERE NOT EXIST (SELECT mytable2.id
              FROM mytable2
             WHERE mytable1.id = mytable2.id);

Thanks.  That works except it should be EXISTS (i.e., plural).

Cheers,
Brook