SQL語句中EXISTS的詳細用法大全

2022-06-23 14:02:16

前言

在業務開展中，會遇到類似需求。

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