我有一条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!
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!
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
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
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
in : = ornot in: != andA in('A','B') means A='A' or A='B'A not in('A','B') means A!='A' and A!='B'