SQL语句中EXISTS的详细用法大全

• 前言
• 一、建表
• • 1.在MySQL数据库建表语句
  • 2.在ORACLE数据库建表语句
• 二、在SELECT语句中使用EXISTS
• • 1.在SQL中使用EXISTS
  • 2.在SQL中使用NOT EXISTS
  • 3.在SQL中使用多个NOT EXISTS
  • 4.在SQL中使用多个EXISTS
  • 5.在SQL中使用NOT EXISTS和EXISTS
• 三、在DELETE语句中使用EXISTS
• • 1.在MySQL中使用
  • 2.在Oracle中使用
• 四、在UPDATE语句中使用EXISTS
• • 1.在MySQL中使用
  • 2.在Oracle中使用

前言

在业务开展中，会遇到类似需求。

需求1：UPDATE表TEST_TB01中的记录；满足条件：这些记录不在TEST_TB02中。

需求2：UPDATE表TEST_TB01中的记录；满足条件：这些记录在TEST_TB02中。

在SQL语句中EXISTS的用法，能够比较简洁的去解决这类需求。

一、建表

1.在MySQL数据库建表语句

CREATE TABLE TEST_TB01(sensor_id BIGINT,part_id BIGINT,flag VARCHAR(64))COMMENT '数据表一';CREATE TABLE TEST_TB02(sensor_id BIGINT,part_id BIGINT,flag VARCHAR(64))COMMENT '数据表二';CREATE TABLE TEST_TB03(sensor_id BIGINT,part_id BIGINT,flag VARCHAR(64))COMMENT '数据表三';

2.在ORACLE数据库建表语句

CREATE TABLE TEST_TB01(sensor_id NUMBER(16),part_id NUMBER(16),flag VARCHAR(64));CREATE TABLE TEST_TB02(sensor_id NUMBER(16),part_id NUMBER(16),flag VARCHAR(64));

二、在SELECT语句中使用EXISTS

在SELECT的SQL语句中使用EXISTS。

在TEST_TB01插入数据：

INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2105,8815,'杭州');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2106,8816,'上海');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2107,8817,'北京');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2108,8818,'深圳');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

在TEST_TB02插入数据：

INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

在TEST_TB03插入数据：

INSERT INTO TEST_TB03 (sensor_id,part_id,flag) VALUES(2106,8816,'上海');INSERT INTO TEST_TB03 (sensor_id,part_id,flag) VALUES(2107,8817,'北京');INSERT INTO TEST_TB03 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

查看TEST_TB01数据：

查看TEST_TB02数据：

查看TEST_TB03数据：

1.在SQL中使用EXISTS

需求：从TEST_TB01中查询出在TEST_TB02中存在的记录，关联条件是两个表的sensor_id相等。

SQL语句：

SELECTaa.sensor_id,aa.part_id,aa.flagFROMTEST_TB01 aaWHERE EXISTS(SELECT 1 FROMTEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id);

执行结果：

2.在SQL中使用NOT EXISTS

需求：从TEST_TB01中查询出在TEST_TB02中不存在的记录，关联条件是两个表的sensor_id相等。

SQL语句：

SELECTaa.sensor_id,aa.part_id,aa.flagFROMTEST_TB01 aaWHERE NOT EXISTS(SELECT 1 FROMTEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id);

执行结果：

3.在SQL中使用多个NOT EXISTS

需求：从TEST_TB01中查询出在TEST_TB02和TEST_TB03中都不存在的记录，关联条件是表的sensor_id相等。

SQL语句：

SELECTaa.sensor_id,aa.part_id,aa.flagFROMTEST_TB01 aaWHERE NOT EXISTS(SELECT 1 FROMTEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id)AND NOT EXISTS(SELECT 1 FROMTEST_TB03 ccWHERE aa.sensor_id = cc.sensor_id);

执行结果：

4.在SQL中使用多个EXISTS

需求：从TEST_TB01中查询出在TEST_TB02和TEST_TB03中都存在的记录，关联条件是表的sensor_id相等。

SQL语句：

SELECTaa.sensor_id,aa.part_id,aa.flagFROMTEST_TB01 aaWHERE EXISTS(SELECT 1 FROMTEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id)AND EXISTS(SELECT 1 FROMTEST_TB03 ccWHERE aa.sensor_id = cc.sensor_id);

执行结果：

5.在SQL中使用NOT EXISTS和EXISTS

需求：从TEST_TB01中查询出在TEST_TB02存在但是TEST_TB03中不存在的记录，关联条件是表的sensor_id相等。

SQL语句：

SELECTaa.sensor_id,aa.part_id,aa.flagFROMTEST_TB01 aaWHERE EXISTS(SELECT 1 FROMTEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id)AND NOT EXISTS(SELECT 1 FROMTEST_TB03 ccWHERE aa.sensor_id = cc.sensor_id);

执行结果：

三、在DELETE语句中使用EXISTS

在DELETE的SQL语句中使用EXISTS和NOT EXISTS。

在TEST_TB01插入数据：

INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2105,8815,'杭州');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2106,8816,'上海');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2107,8817,'北京');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2108,8818,'深圳');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

在TEST_TB02插入数据：

INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

1.在MySQL中使用

需求：从TEST_TB01中删除在TEST_TB02中存在的记录，关联条件是两个表的sensor_id相等。

注意：本例使用MySQL版本：MySQL 5.7.33。

SQL语句：

DELETE FROM TEST_TB01 aaWHERE EXISTS(SELECT 1 FROMTEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id);

执行结果：

结论：在MySQL中是不支持在DELETE的SQL语句中使用EXISTS和NOT EXISTS这种句法。（本例版本：MySQL 5.7.33）。

解决此需求：
SQL语句：

DELETEaaFROMTEST_TB01 aaINNER JOIN TEST_TB02 bbON aa.sensor_id = bb.sensor_id;

注意：在SQL中DELETE后面紧跟着的是需求中需要删除的表名的别名。

如果不使用别名会报错：

2.在Oracle中使用

需求：从TEST_TB01中删除在TEST_TB02中存在的记录，关联条件是两个表的sensor_id相等。

SQL语句：

DELETE FROM TEST_TB01 aaWHERE EXISTS(SELECT 1 FROMTEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id);

执行结果：

执行前TEST_TB01：

执行前TEST_TB02：

执行后TEST_TB01：

四、在UPDATE语句中使用EXISTS

在UPDATE的SQL语句中使用EXISTS。

在TEST_TB01插入数据：

INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2101,8811,'城市');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2102,8812,'城市');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2103,8813,'城市');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2104,8814,'城市');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2105,8815,'城市');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2106,8816,'城市');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2107,8817,'城市');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2108,8818,'城市');INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2109,8819,'城市');

在TEST_TB02插入数据：

INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

1.在MySQL中使用

需求：在TEST_TB01中更新,在TEST_TB02中存在的记录，关联条件是两个表的sensor_id相等。

注意：本例使用MySQL版本：MySQL 5.7.33。

SQL语句：

UPDATE TEST_TB01 aaSET (aa.part_id, aa.flag) =(SELECT bb.part_id, bb.flagFROM TEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id)WHERE EXISTS(SELECT 1 FROM TEST_TB02 cc WHERE aa.sensor_id = cc.sensor_id);

执行结果：

结论：在MySQL中是不支持在UPDATE的SQL语句中使用EXISTS和NOT EXISTS这种句法。（本例版本：MySQL 5.7.33）。

解决此需求：

SQL语句：

UPDATE TEST_TB01 aa ,TEST_TB02 bbSET aa.part_id=bb.part_id,aa.flag=bb.flagWHERE aa.sensor_id = bb.sensor_id;

执行结果：

执行前TEST_TB01：

执行前TEST_TB02：

执行后TEST_TB01：

2.在Oracle中使用

需求：在TEST_TB01中更新,在TEST_TB02中存在的记录，关联条件是两个表的sensor_id相等。

SQL语句：

UPDATE TEST_TB01 aaSET (aa.part_id, aa.flag) =(SELECT bb.part_id, bb.flagFROM TEST_TB02 bbWHERE aa.sensor_id = bb.sensor_id)WHERE EXISTS(SELECT 1 FROM TEST_TB02 cc WHERE aa.sensor_id = cc.sensor_id);

执行结果：

执行前TEST_TB01：

执行前TEST_TB02：

执行后TEST_TB01：