oracle数据库中NOT IN 和 NOT EXISTS为什查不到数据

Question

SELECT GA.ADID, GA.ADNAME
      FROM GAP_AD GA
     WHERE
     exists(SELECT GAP.ADID
              FROM GAP_ADPOSITION GAP
             WHERE GAP.ADPOSITIONSTATUS = '1')
       AND NOT exists(SELECT GAP.ADID
              FROM GAP_ADPOSITION GAP
             WHERE GAP.ADPOSITIONSTATUS = '0'
                OR GAP.ADPOSITIONSTATUS = '2')
为什么查不到数据呢？
SELECT GA.ADID, GA.ADNAME
      FROM GAP_AD GA
     WHERE GA.ADID IN(SELECT GAP.ADID
              FROM GAP_ADPOSITION GAP
             WHERE GAP.ADPOSITIONSTATUS = '1')
       AND GA.ADID NOT IN (SELECT GAP.ADID
              FROM GAP_ADPOSITION GAP
             WHERE GAP.ADPOSITIONSTATUS = '0'
                OR GAP.ADPOSITIONSTATUS = '2')
这个就可以查到
哪位朋友帮帮我

Answer 1

SELECT GA.ADID, GA.ADNAME
FROM GAP_AD GA
WHERE
exists(SELECT GAP1.ADID
FROM GAP_ADPOSITION GAP1
WHERE GAP1.ADPOSITIONSTATUS = '1' and GA.ADID=GAP1.ADID)
AND NOT exists(SELECT GAP2.ADID
FROM GAP_ADPOSITION GAP2
WHERE (GAP2.ADPOSITIONSTATUS = '0'
OR GAP2.ADPOSITIONSTATUS = '2')AND GA.ADID=GAP2.ADID )

LZ，have a try!