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
查詢,可以看到相同的結果,如下範例中所示。
以下是針對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'
。
以下是針對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)