MySQL 中的 INTERSECT 運算子
本文將幫助你瞭解 INTERSECT
運算子。雖然 MySQL 不支援 INTERSECT
和 MINUS
/EXCEPT
,但還有其他方法可以模擬此功能。
我們將看到 INTERSECT
是什麼,它的好處,以及學習如何在 MySQL 中模擬 INTERSECT
的各種方法。
MySQL 中的 INTERSECT
運算子簡介
INTERSECT
是一個集合運算子,用於從兩個集合中檢索公共元素。它還用於從兩個表中獲取 DISTINCT
(或常見)記錄(行)。
我們也可以說 INTERSECT
運算子只返回相同的行,這些行作為兩個 SELECT
語句的輸出檢索。看看下面的維恩圖來理解 INTERSECTION
。
在這裡,黃色網格區域是 INTERSECTION
。INTERSECT
的主要好處是你可以從許多表中訪問相同的記錄。
儘管 MySQL
不支援 INTERSECT
運算子,但我們可以使用其他替代方法來完成此功能。
MySQL 中的 INTERSECT 運算子
如前所述,MySQL
中沒有 INTERSECT
運算子。儘管如此,我們可以根據查詢的複雜性和要求使用 INNER JOIN
和 IN
子句以及 EXISTS
子句來模擬這一點。
我們正在使用兩個名為 order
和 customer
的表。customer
表的欄位包括 customer_id
、customer_firstname
、customer_lastname
、customer_age
和 customer_salary
。
order
表有 order_id
、order_date
、order_amount
和 customer_id
(customer_id
是這裡的外來鍵)。我們的 customer
表和 order
表的資料如下所示。
你可以使用以下示例程式碼建立兩個表並插入資料。
#create customer table
CREATE TABLE customer(
customer_id INT NOT NULL PRIMARY KEY,
customer_firstname VARCHAR(60) NOT NULL,
customer_lastname VARCHAR(60) NOT NULL,
customer_age INT NOT NULL,
customer_salary INT NOT NULL
);
#create order table
CREATE TABLE order(
order_id INT NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL,
order_amount INT NOT NULL,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
#insert into customer table
INSERT INTO customer VALUES
(1, 'Shajeel', 'Daniel', 23, 9000),
(2, 'Nayya', 'Preston', 54, 1500),
(3, 'James', 'Robert', 36, 2500),
(4, 'Jennifer', 'John', 29, 5000),
(5, 'Sarah', 'Paul', 15, 8000),
(6, 'Karen', 'Donald', 40, 3500);
#insert into order table
INSERT INTO order VALUES
(1, '2019-12-03 10:25:30', 500, 2),
(2, '2019-12-10 12:00:30', '1500', 4);
客戶表:
訂單表:
在 MySQL 中通過 INNER JOIN
模擬 INTERSECT
我們想找到訂單
詳細資訊(order_id
、order_amount
、order_date
)和客戶
詳細資訊(customer_id
、customer_firstname
、customer_lastname
),以瞭解哪個客戶
放置了在什麼日期訂購
。
這意味著我們要找到與 customer
表和 order
表相同的 customer
。還需要觀察資料來自兩個表;我們可以使用名為 INNER JOIN
的 join。
#MySQL Version 8.0.27
SELECT
order.order_id, customer.customer_id, customer.customer_firstname,
customer.customer_lastname,order.order_amount,order.order_date
FROM order
INNER JOIN
customer ON order.customer_id = customer.customer_id;
在上面的程式碼中,它將檢索 customer_id
在 customer
表和 order
表中的 customer_id
相同的那些 order_id
、customer_id
、customer_firstname
、customer_lastname
、order_amount
和 order_date
.
輸出:
通過 MySQL 中的 IN
子句模擬 INTERSECT
現在,我們有不同的情況。在這裡,我們只需要與客戶相關的資料。
該資料包括 customer_id
、customer_firstname
、customer_lastname
和 customer_age
。並且該客戶必須出現在 order
表中。
在這裡,我們可以使用 IN
子句來模擬 INTERSECT
操作。
#MySQL version 8.0.27
SELECT
customer.customer_id, customer.customer_firstname,
customer.customer_lastname, customer.customer_age
FROM customer
WHERE customer.customer_id IN ( SELECT order.customer_id FROM order);
子查詢將首先通過從上面的 order
表中收集所有 customer_id
來執行。然後它將僅選擇
那些在子查詢結果中出現 customer_id
的客戶相關詳細資訊。
輸出:
通過 MySQL 中的 EXISTS
子句模擬 INTERSECT
在這種情況下,我們只需要年齡小於 45
並且必須至少下一個訂單
的客戶
詳細資訊。EXISTS
子句以下列方式使用。
如果省略 EXISTS
子句,以下程式碼也將產生相同的輸出。
SELECT
customer.customer_id, customer.customer_firstname,
customer.customer_lastname, customer.customer_age
FROM customer
WHERE customer.customer_age < 45
AND EXISTS
(SELECT order.customer_id FROM order where order.customer_id = customer.customer_id);
子查詢將首先執行,並給出 order
表中的所有 customer_id
,其中 customer_id
在兩個表(order
和 customer
表)中是相同的。然後它將僅選擇
那些年齡小於 45
和 customer_id
的客戶相關詳細資訊出現在子查詢結果中。
輸出:
まとめ
本文總結了執行一項操作的許多其他替代方案。
我們還學習了在 MySQL 中模擬 INTERSECT
的不同方法。它包括 INNER JOIN
、IN
子句和 EXISTS
子句。
我們甚至看到了如何在 MySQL 中模擬 INTERSECT
操作時使用 WHERE
和 AND
。