文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT

2024-04-02 19:55

关注

前言

最近几年,mysql 不断致力于兼容 SQL 标准。例如 MySQL 8.0 中的窗口函数、通用表表达式、检查约束等等。

最新发布的 MySQL 8.0.31 继续对 SQL 语句进行了增强,提供了缺失已久的两个集合操作符:INTERSECT 和 EXCEPT。

交集操作符(INTERSECT)

INTERSECT 操作符用于返回两个查询结果中的共同部分,即同时出现在第一个查询结果和第二个查询结果中的数据,并且对最终结果进行了去重操作。交集运算的示意图如下:

MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT

其中,1 和 2 是两个查询中都存在的数据;因此交集运算的结果只包含 1 和 2。

我们创建一个年度优秀员工表(excellent_emp),用于演示集合操作:

CREATE TABLE excellent_emp(
    year   INT NOT NULL, 
    emp_id INTEGER NOT NULL,
    CONSTRAINT pk_excellent_emp PRIMARY KEY (YEAR, emp_id)
);
 
INSERT INTO excellent_emp VALUES (2018, 9);
INSERT INTO excellent_emp VALUES (2018, 11);
INSERT INTO excellent_emp VALUES (2019, 9);
INSERT INTO excellent_emp VALUES (2019, 20);

以下示例用于查找 2018 年和 2019 年都是优秀员工的员工编号:

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
INTERSECT
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019;
 
emp_id|
------|
     9|

其中,INTERSECT 表示交集运算。第一个查询语句返回了 9 和 11,第二个查询语句返回了 9 和 20,最终结果返回共同的 9。集合操作返回的字段名由第一个语句决定,此处两个语句拥有相同的字段名(emp_id)。

对于 MySQL 5.7 以及之前的版本,以上示例可以改写为等价的连接查询:

SELECT t1.emp_id
  FROM excellent_emp t1
  JOIN excellent_emp t2
    ON (t1.emp_id = t2.emp_id
        AND t1.year = 2018
        AND t2.year = 2019);
 
emp_id|
------|
     9|

交集运算都可以改写为等价的等值内连接查询。

INTERSECT 操作符的完整语法如下:

SELECT ...
INTERSECT [ALL | DISTINCT] SELECT ...
[INTERSECT [ALL | DISTINCT] SELECT ...]

ALL 选项表示保留查询结果集中的重复记录,DISTINCT 选项表示去除查询结果集中的重复记录,默认选项为 DISTINCT。

另外,INTERSECT 操作符的优先级比 UNION 和 EXCEPT 更高,因此以下两种写法等价:

TABLE r EXCEPT TABLE s INTERSECT TABLE t;

TABLE r EXCEPT (TABLE s INTERSECT TABLE t);

关于 INTERSECT 操作符的更多信息,可以参考官方文档。

差集操作符(EXCEPT)

EXCEPT 操作符用于返回出现在第一个查询结果中,但不在第二个查询结果中的记录,并且对最终结果进行了去重操作。差集运算的示意图如下:

MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT

第一个查询的结果中只有 3 没有出现在第二个查询的结果中,因此差集运算的结果只保留了 3。

以下语句查找 2019 年被评为优秀,但是 2018 年不是优秀的员工:

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
EXCEPT
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018;
 
emp_id|
------|
    20|

查询结果显示,只有 20 号员工是 2019 年新晋的优秀员工。

对于 MySQL 5.7 以及之前的版本,以上示例可以改写为等价左外连接查询:

SELECT t1.emp_id
  FROM excellent_emp t1
  LEFT JOIN excellent_emp t2 ON (t1.emp_id = t2.emp_id AND t2.year = 2018)
 WHERE t1.year = 2019
   AND t2.emp_id IS NULL;
 
emp_id|
------|
    20|

其中,左外连接返回了所有的优秀员工;然后利用 WHERE 条件找出其中 2019 年是优秀但 2018 年不是优秀的员工。

EXCEPT 操作符的完整语法如下:

SELECT ...
EXCEPT [ALL | DISTINCT] SELECT ...
[EXCEPT [ALL | DISTINCT] SELECT ...]

ALL 选项表示保留查询结果集中的重复记录,DISTINCT 选项表示去除查询结果集中的重复记录,默认选项为 DISTINCT。

关于 INTERSECT 操作符的更多信息,可以参考官方文档。

总结

到此这篇关于MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT的文章就介绍到这了,更多相关MySQL8.0 INTERSECT和EXCEPT内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯