【DB2】NOT IN使用中的大坑

1.环境准备

------建表TB
DROP TABLE TB;

CREATE TABLE TB
    (
    ID           INTEGER,
    LEVEL_DETAIL VARCHAR (100)
    );
INSERT INTO TB (ID, LEVEL_DETAIL)
VALUES (1, 'A');

INSERT INTO TB (ID, LEVEL_DETAIL)
VALUES (NULL, 'A');

INSERT INTO TB (ID, LEVEL_DETAIL)
VALUES (2, 'B');

INSERT INTO TB (ID, LEVEL_DETAIL)
VALUES (1, 'A++');

INSERT INTO TB (ID, LEVEL_DETAIL)
VALUES (1, 'A--');
----建表TB_TEMP
DROP TABLE TB_TEMP;

CREATE TABLE TB_TEMP
    (
    ID           INTEGER,
    LEVEL_DETAIL VARCHAR (100)
    );

INSERT INTO TB_TEMP (ID, LEVEL_DETAIL)
VALUES (1, 'A');

INSERT INTO TB_TEMP (ID, LEVEL_DETAIL)
VALUES (2, 'B');

INSERT INTO TB_TEMP (ID, LEVEL_DETAIL)
VALUES (1, 'A++');

INSERT INTO TB_TEMP (ID, LEVEL_DETAIL)
VALUES (1, 'A--');

INSERT INTO TB_TEMP (ID, LEVEL_DETAIL)
VALUES (3, 'CCC');

INSERT INTO TB_TEMP (ID, LEVEL_DETAIL)
VALUES (4, 'CCC');

INSERT INTO TB_TEMP (ID, LEVEL_DETAIL)
VALUES (4, 'C');

INSERT INTO TB_TEMP (ID, LEVEL_DETAIL)
VALUES (5, 'AA');

数据展示：

TB表

TB_TEMP表

---使用IN
SELECT * FROM TB_TEMP WHERE ID IN (SELECT ID FROM TB GROUP BY ID)

 

--EXISTS
SELECT * FROM TB_TEMP A WHERE EXISTS (SELECT 1 FROM TB B WHERE A.ID=B.ID)

 

--NOT IN (跟预期结果不一致)
SELECT * FROM TB_TEMP WHERE ID NOT IN (SELECT ID FROM TB GROUP BY ID)

预期结果：

ID	LEVEL_DETAIL
3	CCC
4	CCC
4	C
5	AA

实际结果：

--NOT EXISTS(没问题)
SELECT * FROM TB_TEMP A WHERE NOT EXISTS (SELECT 1 FROM TB B WHERE A.ID=B.ID)

在使用过程中，尽量不要使用NOT IN ,当列中出现了NULL值,那么将会无结果返回（NULL不是具体的值，做二元运算符时,计算结果也为NULL）