MySQL交集INTERSECT運算子

在本教學中，我們將向您介紹SQL INTERSECT運算子，並展示如何模擬MySQL INTERSECT運算子(交集)。

1. SQL INTERSECT操作符簡介

INTERSECT運算子是一個集合運算子，它只返回兩個查詢或更多查詢的交集。

以下說明INTERSECT運算子的語法。

(SELECT column_list 
FROM table_1)
INTERSECT
(SELECT column_list
FROM table_2);

INTERSECT運算子比較兩個查詢的結果，並返回由左和右查詢輸出的不同行記錄。

要將INTERSECT運算子用於兩個查詢，應用以下規則：

• 列的順序和數量必須相同。
• 相應列的資料型別必須相容或可轉換。

下圖說明了INTERSECT運算子。

左側查詢產生一個結果集(1,2,3)，右側查詢返回一個結果集(2,3,4)。

INTERSECT操作符返回包含(2,3)，也就是兩個結果集的相叉的行記錄。與UNION運算符不同，INTERSECT運算子返回兩個集合之間的交點。

請注意，SQL標準有三個集合運算子，包括UNION，INTERSECT和MINUS。

2. MySQL INTERSECT模擬

不幸的是，MySQL不支援INTERSECT操作符。 但是我們可以模擬INTERSECT操作符。

我們為演示建立一些範例資料。

以下語句建立表t1和t2，然後將資料插入到兩個表中。

USE testdb;

DROP TABLE IF exists t1;
DROP TABLE IF exists t2;

CREATE TABLE t1 (
    id INT PRIMARY KEY
);

CREATE TABLE t2 LIKE t1;

INSERT INTO t1(id) VALUES(1),(2),(3);

INSERT INTO t2(id) VALUES(2),(3),(4);

以下從t1表查詢返回行記錄如下 -

mysql> SELECT id FROM t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set

以下從t2表查詢返回行記錄如下 -

mysql> SELECT id FROM t2;
+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
+----+
3 rows in set

使用DISTINCT運算子和INNER JOIN子句模擬MySQL INTERSECT運算子

以下語句使用DISTINCT運算子和INNER JOIN子句來返回兩個表中的相交集合：

SELECT DISTINCT 
   id 
FROM t1
   INNER JOIN t2 USING(id);

執行上面查詢語句，得到以下結果 -

+----+
| id |
+----+
|  2 |
|  3 |
+----+
2 rows in set

上面語句是怎麼工作的？

• INNER JOIN子句從左表和右表返回所有符合條件的行記錄。
• DISTINCT運算子刪除重複行。

使用IN運算子和子查詢模擬MySQL INTERSECT運算子

以下語句使用IN運算子和子查詢返回兩個結果集的交集。

SELECT DISTINCT
    id
FROM
    t1
WHERE
    id IN (SELECT 
            id
        FROM
            t2);

執行以上查詢語句，得到以下結果 -

+----+
| id |
+----+
|  2 |
|  3 |
+----+
2 rows in set

上面查詢語句是如何工作的？

• 子查詢返回第一個結果集。
• 外部查詢使用IN運算子僅選擇第一個結果集中的值。DISTINCT運算子確保只選擇不同的值。

在本教學中，您已經學習了幾種方法來模擬MySQL中的INTERSECT(交集)運算子。