MariaDB 教程

数据库和表

MariaDB操作符

MariaDB高级部分

original icon
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.knowledgedict.com/tutorial/mariadb-intersect-operator.html

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)