MariaDB Intersect運算符

INTERSECT運算符用於返回2個或更多表的交集。 如果兩個表中都存在記錄，它將被包含在INTERSECT結果中。 否則，它將從INTERSECT結果中被省略。

語法

SELECT expression1, expression2, ... expression_n  
FROM tables  
[WHERE conditions]  
INTERSECT  
SELECT expression1, expression2, ... expression_n  
FROM tables  
[WHERE conditions];

說明圖如下所示 -

注意：MariaDB不支持INTERSECT運算符，但是通過使用IN運算符來模擬INTERSECT查詢，可以看到相同的結果，如下示例中所示。

1. INTERSECT運算符返回單個字段

以下是針對INTERSECT運算符的一般查詢，但不會在MariaDB中超混雜。

假設有兩張表:students和teachers。對應的表結構和數據如下 -

students表中的數據：

MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          1 | Maxsu        | Haikou          | 2017-01-07     |
|          2 | JMaster      | Beijing         | 2016-05-07     |
|          3 | Mahesh       | Guangzhou       | 2016-06-07     |
|          4 | Kobe         | Shanghai        | 2016-02-07     |
|          5 | Blaba        | Shenzhen        | 2016-08-07     |
+------------+--------------+-----------------+----------------+
5 rows in set (0.00 sec)

teachers表中的數據：

USE testdb;
DROP TABLE teachers;
CREATE TABLE teachers(  
    teacher_id INT NOT NULL AUTO_INCREMENT,  
    name VARCHAR(100) NOT NULL,  
    address VARCHAR(40) NOT NULL,  
    admission_date DATE,  
    PRIMARY KEY ( teacher_id )
);
-- 插入數據
INSERT INTO teachers  
(teacher_id, name, address, admission_date)  
VALUES(1,'Maxsu','Haikou','2013-06-07 00:00:00');

INSERT INTO teachers  
(teacher_id, name, address, admission_date)  
VALUES(2,'張天經','廣州','2013-08-08 00:00:00');

INSERT INTO teachers  
(teacher_id, name, address, admission_date)  
VALUES(3,'李四光','三亞','2014-09-07 00:00:00');

經過上創建和插入數據，現在teachers表中擁有以下數據記錄 -

MariaDB [testdb]> select * from teachers;
+------------+--------+---------+----------------+
| teacher_id | name   | address | admission_date |
+------------+--------+---------+----------------+
|          1 | Maxsu  | Haikou  | 2013-06-07     |
|          2 | 張天經 | 廣州    | 2013-08-08     |
|          3 | 李四光 | 三亞    | 2014-09-07     |
+------------+--------+---------+----------------+
3 rows in set (0.00 sec)

假設現在要查詢學生和老師的姓名有哪幾個？參考以下查詢語句 -

-- 理想語句，但是MariaDB中不支持 
SELECT student_name  
FROM Students
INTERSECT  
SELECT name  
FROM teachers;

上面語句的代替語句是 -

SELECT s.student_name AS name
FROM Students s
WHERE s.student_name IN (SELECT t.name FROM teachers t);

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

MariaDB [testdb]> SELECT s.student_name AS name
    -> FROM Students s
    -> WHERE s.student_name IN (SELECT t.name FROM teachers t);
+-------+
| name  |
+-------+
| Maxsu |
+-------+
1 row in set (0.10 sec)

它顯示了兩個表中name列都有存在的值 - 'Maxsu'。

2. INTERSECT運算符指定WHERE條件

以下是針對INTERSECT運算符的一般查詢，但不能在MariaDB中正常使用。參考以下語句 -

-- 理想語句，但是MariaDB中不支持 
SELECT s.student_name  
FROM Students s WHERE student_id<100
INTERSECT  
SELECT t.name  
FROM teachers t
WHERE t.address = 'Haikou';

上面語句的代替語句是 -

SELECT s.student_name AS name
FROM Students s
WHERE s.student_id<100 AND s.student_name IN (SELECT t.name FROM teachers t  WHERE t.address = 'Haikou');

執行上面示例代碼，得到以下結果 -

MariaDB [testdb]> SELECT s.student_name AS name
    -> FROM Students s
    -> WHERE s.student_id<100 AND s.student_name IN (SELECT t.name FROM teachers t  WHERE t.address = 'Haikou');
+-------+
| name  |
+-------+
| Maxsu |
+-------+
1 row in set (0.00 sec)