【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）

巴特西

【DB2】NOT IN使用中的大坑

1.环境准备

最新文章

热门文章