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中超混雜。

假設有兩張表:studentsteachers。對應的表結構和資料如下 -

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)