【DB2】NOT IN使用中的大坑

最新推荐文章于 2024-02-29 19:33:11 发布

转载最新推荐文章于 2024-02-29 19:33:11 发布 · 282 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/OliverQin/p/6735324.html

文章标签：

#数据库

本文通过创建两个表并插入数据，对比了IN、EXISTS、NOT IN和NOT EXISTS等SQL查询方式的效果，特别指出NOT IN在遇到NULL值时可能出现的问题。

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');