Thread: NOT-IN CLAUSE

Hi,

My Following select st. is running from last one day. I think the problem is
NOT IN clause.
Please suggest the alternative select clause.



SELECT resreq.proj, resreq.ver, substr(resreq.act, -5, 5) act, resreq.rn,
null,
       null, resreq.rl, resreq.dr, resreq.ra
    FROM resreq
    WHERE resreq.ver IN ('0', '99')
      AND NOT (resreq.proj, resreq.ver, substr(resreq.act, -5, 5),
resreq.rn)
          IN (SELECT actres.proj, actres.ver, substr(actres.act, -5, 5) act,

                      actres.rn
               FROM actres, resreq
               WHERE actres.proj = resreq.proj
                 AND actres.ver = resreq.ver
                 AND substr(actres.act, -5, 5) = substr(resreq.act, -5, 5)
                 AND actres.rn = resreq.rn
                 AND actres.ver IN ('0', '99'))




Best Regards,
Prashant Dalal.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
LaborLogix

On Mon, Dec 15, 2003 at 12:14:17 -0500, Prashant Dalal <pdalal@laborlogix.com> wrote:
> Hi,
> 
> My Following select st. is running from last one day. I think the problem is
> NOT IN clause.
> Please suggest the alternative select clause.

NOT IN was especially slow up until Postgres 7.4. If using 7.4 is a problem
you can rewrite your query to use NOT EXISTS.