oracle query to check not exists in other table

Question

I have following query:

select vw.CONFERENCEID, alert.ALERTID, del.CHANNELID, del.DOCUMENTTEMPLATEID,
       vw.starttime, vw.CONFERENCEID, alert.ALERTATTRIBUTEID,
       alert.ALERTCATEGORYID, alert.ATTACHMENT, alert.ATTACHMENTLOCATION,
       alert.ATTACHMENTNAME
  from TBLMCONFERENCE vw,
       TBLMSYSTEMALERTS alert,
       TBLMALERTDELIVERYREL del,
       tblmstandardmaster sm 
 WHERE alert.Alertid = del.alertid
   and sm.masterid = del.CHANNELID
   and alert.SYSTEMGENERATED = 'N'
   and alert.alertid not in (
       select sent.ALERTID
         from TBLMSENTALERTHISTORY sent
        where sent.REFACCOUNTID = vw.conferenceid
          and sent.ALERTID = alert.ALERTID
          and sent.CHANNELID = del.CHANNELID
          and sent.RESETFLAG = 'N')

I am getting records even when a matching record exists in TBLMSENTALERTHISTORY table.

Is there anything wrong in this query?

Have you tried firing the inner query and checking to see that the alertid is present in the result? — Chetter Hummin
– Chetter Hummin, Commented Aug 29, 2012 at 15:34

Vincent Malgrat · Accepted Answer · 2012-08-29 16:11:23Z

I'm not sure why you are getting data, maybe you could build a small example with a couple of tables and inserts.

However, I can point that your use of the NOT IN operator is not standard: in most cases you should not join the subquery to the main query with IN and NOT IN operators (it is redundant). Instead you would write:

SELECT *
  FROM TBLMCONFERENCE vw,
       TBLMSYSTEMALERTS alert,
       TBLMALERTDELIVERYREL del,
       tblmstandardmaster sm
 WHERE alert.Alertid = del.alertid
   AND sm.masterid = del.CHANNELID
   AND alert.SYSTEMGENERATED = 'N'
   AND (alert.alertid, del.CHANNELID, vw.conferenceid)
       NOT IN (SELECT sent.ALERTID, sent.CHANNELID, sent.conferenceid
                 FROM TBLMSENTALERTHISTORY sent
                WHERE sent.RESETFLAG = 'N')

In your case you could use your subquery directly with NOT EXISTS:

SELECT *
  FROM TBLMCONFERENCE vw,
       TBLMSYSTEMALERTS alert,
       TBLMALERTDELIVERYREL del,
       tblmstandardmaster sm
 WHERE alert.Alertid = del.alertid
   AND sm.masterid = del.CHANNELID
   AND alert.SYSTEMGENERATED = 'N'
   AND NOT EXISTS
          (SELECT sent.ALERTID
             FROM TBLMSENTALERTHISTORY sent
            WHERE sent.REFACCOUNTID = vw.conferenceid
              AND sent.ALERTID = alert.ALERTID
              AND sent.CHANNELID = del.CHANNELID
              AND sent.RESETFLAG = 'N')

Note that in general NOT IN and NOT EXISTS are not equivalent, because of NULLs.

Collectives™ on Stack Overflow

oracle query to check not exists in other table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related