SQL语句中EXISTS的用法

2022-08-13 16:34:38

记录：258

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

需求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语句：

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

执行结果：

2.在SQL中使用NOT EXISTS

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

SQL语句：

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE NOT EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

执行结果：

3.在SQL中使用多个NOT EXISTS

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

SQL语句：

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE NOT EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id)
  AND NOT EXISTS
  (SELECT 1 FROM
    TEST_TB03 cc
  WHERE aa.sensor_id = cc.sensor_id);

执行结果：

4.在SQL中使用多个EXISTS

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

SQL语句：

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE  EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id)
  AND  EXISTS
  (SELECT 1 FROM
    TEST_TB03 cc
  WHERE aa.sensor_id = cc.sensor_id);

执行结果：

5.在SQL中使用NOT EXISTS和EXISTS

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

SQL语句：

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE  EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id)
  AND NOT EXISTS
  (SELECT 1 FROM
    TEST_TB03 cc
  WHERE 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 aa
WHERE EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

执行结果：

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

解决此需求：

SQL语句：

DELETE
  aa
FROM
  TEST_TB01 aa
 INNER JOIN TEST_TB02 bb
    ON aa.sensor_id = bb.sensor_id;

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

如果不使用别名会报错：

2.在Oracle中使用

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

SQL语句：

DELETE  FROM 
TEST_TB01 aa
WHERE EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE 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 aa
   SET (aa.part_id, aa.flag) =
       (SELECT bb.part_id, bb.flag
          FROM TEST_TB02 bb
         WHERE 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 bb
SET 
    aa.part_id=bb.part_id,
    aa.flag=bb.flag
WHERE aa.sensor_id = bb.sensor_id;

执行结果：

执行前TEST_TB01：

执行前TEST_TB02：

执行后TEST_TB01：

2.在Oracle中使用

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

SQL语句：

UPDATE TEST_TB01 aa
   SET (aa.part_id, aa.flag) =
       (SELECT bb.part_id, bb.flag
          FROM TEST_TB02 bb
         WHERE 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：

以上，感谢。

码农公寓

相关文章