用什么可以代替not in语句？

[ondogdog]

我有一条sql语句，

SELECT DISTINCT POWS,JONO
FROM TMP_TABLE
WHERE QTY>0 AND DEPT='AA' AND CELL='BB' AND
(POWS NOT IN (SELECT POWS_P FROM TABLE1 WHERE DEPT_P='AA' AND CELL_P='BB')) AND
(JONO NOT IN (SELECT JONO_J FROM TABLE2 WHERE DEPT_J='AA' AND CELL_J='BB'))
ORDER BY JONO

我的目的是想用另一条sql语句来代替not in的，既语句中不出现not in！

[sgucxc0]

in : = or

not in: != and

A in('A','B') means A='A' or A='B'

A not in('A','B') means A!='A' and A!='B'

[中国风]

not exists

[dulei115]

SELECT DISTINCT a.POWS, a.JONO
FROM TMP_TABLE a
left join (SELECT POWS_P FROM TABLE1 WHERE DEPT_P='AA' AND CELL_P='BB') b
on a.POWS = b.POWS
left join (SELECT JONO_J FROM TABLE2 WHERE DEPT_J='AA' AND CELL_J='BB') c
on a.JONO = c.JONO
WHERE a.QTY>0 AND a.DEPT='AA' AND a.CELL='BB' AND
b.POWS is null AND b.JONO is null
ORDER BY a.JONO

[selectplayer]

SELECT DISTINCT A.POWS,A.JONO
FROM TMP_TABLE A
LEFT JOIN POWS_P B ON B.DEPT_P='AA' AND B.CELL_P='BB' AND B.POWS_P=A.POWS
LEFT JOIN JONO_J C ON C.DEPT_J='AA' AND C.CELL_J='BB' AND C.JONO_J=A.JONO
WHERE QTY>0 AND DEPT='AA' AND CELL='BB' AND B.B.POWS_P IS NULL AND C.JONO_J IS NULL
ORDER BY JONO

[Hahahahahaha]

SELECT DISTINCT POWS,JONO
FROM TMP_TABLE a
WHERE QTY>0 AND DEPT='AA' AND CELL='BB' AND
not exists (
SELECT 1 FROM TABLE1 WHERE DEPT_P='AA' AND CELL_P='BB' AND POWS_P=a.POWS
) AND
not exists (
SELECT 1 FROM TABLE2 WHERE DEPT_J='AA' AND CELL_J='BB' AND JONO_J=a.JONO
)
ORDER BY JONO

[jasonren]

not exsits