五种主流数据库：集合运算

关系型数据库中的表与集合理论中的集合类似，表是由行（记录）组成的集合。因此，SQL 支持基于数据行的各种集合运算，包括并集运算（Union）、交集运算（Intersect）和差集运算（Except）。它们都可以将两个查询的结果集合并成一个结果集，但是合并的规则各不相同。

本文比较了五种主流数据库实现的集合运算，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

功能MySQLOracleSQL ServerPostgreSQLSQLite
INTERSECT✔️✔️✔️✔️✔️
INTERSECT ALL✔️✔️❌✔️❌
UNION✔️✔️✔️✔️✔️
UNION ALL✔️✔️✔️✔️✔️
EXCEPT✔️✔️✔️✔️✔️
EXCEPT ALL✔️✔️❌✔️❌

执行 SQL 集合运算时，集合操作中的两个查询结果需要满足以下条件：

  • 两个查询结果集中字段的数量必须相同。
  • 两个查询结果集中对应字段的类型必须匹配或兼容。SQLite 使用动态数据类型，不要求字段类型匹配或兼容。

也就是说，参与运算的两个查询结果集的字段结构必须相同。如果一个查询返回 2 个字段，另一个查询返回 3 个字段，肯定无法进行合并。如果一个查询返回数字类型的字段，另一个查询返回字符类型的字段，通常也无法进行合并；不过，某些数据库（例如 MySQL）可能会尝试执行隐式的类型转换。

交集求同

SQL 交集运算的运算符是 INTERSECT，它可以用于获取两个查询结果集中的共同部分，也就是同时出现在第一个查询结果集和第二个查询结果集中的数据，如下图所示：

在这里插入图片描述
图 中的 1 和 2 是两个查询结果集中都存在的元素，因此交集运算的结果只包含 1 和 2。

SQL 交集运算的语法如下：

SELECT column1, column2, ...
FROM table1
INTERSECT [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;

其中，DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。ALL 表示保留合并结果中的重复记录。如果省略，默认值为 DISTINCT。

注意：MySQL 8.0开始支持 INTERSECT 运算符以及 ALL 选项。PostgreSQL 支持完整的 DISTINCT 和 ALL 选项，Oracle 21c 开始支持 ALL 选项，SQL Server 以及 SQLite 支持简写的 INTERSECT。

我们首先创建两个简单的测试表 t_set1 和 t_set2。

CREATE TABLE t_set1
(id INTEGER,name VARCHAR(10)
);
INSERT INTO t_set1 VALUES (1, 'apple');
INSERT INTO t_set1 VALUES (2, 'banana');
INSERT INTO t_set1 VALUES (3, 'orange');
CREATE TABLE t_set2
(id INTEGER,name VARCHAR(10)
);
INSERT INTO t_set2 VALUES (1, 'apple');
INSERT INTO t_set2 VALUES (2, 'banana');
INSERT INTO t_set2 VALUES (4, 'pear');

然后使用以下语句查找两个表中的共同记录。

SELECT id, name
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;

查询返回的结果如下：

id|name 
--|------1|apple 2|banana

其中，“apple”和“banana”是两个表中的共同数据。

以上示例中两个 SELECT 语句返回的列名都是 id 和 name，因此最终结果返回的列表也是 id 和 name。如果两个语句返回的列名不同，最终结果使用第一个语句返回的列名。

通常来说，交集运算都可以改写为等价的内连接查询。上面的查询语句可以改写为下面这样：

SELECT DISTINCT t1.id, t1.name
FROM t_set1 t1
JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name);

注意，SELECT 列表中返回的全部字段（id 和 name）都必须作为连接查询的条件。

前文我们提到过，使用 SQL 集合运算的前提是，参与集合运算的两个查询结果集必须包含相同数量的字段，并且对应字段的数据类型必须匹配。因此，以下两个示例都会返回错误：

SELECT id
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;SELECT id, id
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;

在第一个示例中，两个 SELECT 语句返回的字段数量不相同；在第二个示例中，两个 SELECT 语句返回的字段数据类型不一致。对于第二个查询示例，SQLite 不会返回错误。

并集存异

SQL 并集运算的运算符是 UNION，它可以用于计算两个查询结果集的相加，返回出现在第一个查询结果集或者第二个查询结果集中的数据，如下图所示。

在这里插入图片描述
图中的 1 和 2 是两个查询结果集中都存在的元素，不过它们在最终结果中只出现了一次，因为 UNION 运算符排除了查询结果中的重复记录。

SQL 并集运算的语法如下：

SELECT column1, column2, ...
FROM table1
UNION [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;

其中，DISTINCT 表示对合并的结果集进行去重操作，只保留不重复的记录。ALL 表示保留最终结果中的重复记录。如果省略，默认值为 DISTINCT。

以下是一个 UNION 运算符的示例：

SELECT id, name
FROM t_set1 
UNION
SELECT id, name
FROM t_set2;

查询返回的结果如下：

id|name 
--|------1|apple 2|banana3|orange4|pear

虽然“apple”和“banana”在两个表中都存在，但是它们在最终的结果中只出现了一次。

UNION 运算符可以改写为等价的全外连接查询。例如，上面的查询语句可以改写为下面这样：

-- Oracle、Microsoft SQL Server、PostgreSQL 以及 SQLite 
SELECT COALESCE(t1.id, t2.id), COALESCE(t1.name, t2.name)
FROM t_set1 t1
FULL JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name);

其中，全外连接可以返回左表和右表中的全部数据，COALESCE 函数的作用就是当左表字段为空时返回右表中的字段。MySQL 目前不支持全外连接查询。

如果我们想要保留并集运算结果中的重复记录，可以使用 UNION ALL 运算符。例如：

SELECT id, name
FROM t_set1 
UNION ALL
SELECT id, name
FROM t_set2;

查询返回的结果如下：

id|name 
--|------1|apple 2|banana3|orange1|apple 2|banana4|pear

此时，“apple”和“banana”在结果中分别出现了两次。

提示：通常来说，UNION ALL 运算符无须进行重复值的去除，其性能比 UNION 运算符更好（尤其在数据量比较大的情况下）。

对于 UNION 和 UNION ALL 运算符，两个查询结果必须包含相同数量的字段，同时对应字段的数据类型也要兼容。不过，MySQL 和 SQLite 会执行隐式的数据类型转换，例如：

-- MySQL 和 SQLite
SELECT 1 AS id
UNION ALL
SELECT 'sql' AS name;

MySQL 将第一个查询返回的字段转换为字符串类型，SQLite 将第二个查询返回的字段转换为整数类型。查询返回的结果如下：

id 
---
1 
sql

差集排他

SQL 差集运算的运算符是 EXCEPT，它可以用于计算两个查询结果集的相减，返回出现在第一个查询结果集中但不在第二个查询结果集中的数据，如下图所示。

在这里插入图片描述

图 中第一个查询的结果只有元素 3 没有出现在第二个查询的结果中，因此差集运算的结果只返回了 3。SQL 差集运算的语法如下：

SELECT column1, column2, ...
FROM table1
EXCEPT [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;

其中，DISTINCT 表示对合并的结果集进行去重操作，只保留不重复的记录。ALL 表示保留最终结果集中的重复记录。如果省略，默认值为 DISTINCT。

注意：MySQL 8.0 开始支持 EXCEPT 运算符以及 ALL 选项。Oracle 21c 开始支持 EXCEPT 关键字以及 ALL 选项，其以前的版本使用等价的 MINUS 运算符。PostgreSQL 支持完整的 DISTINCT 和 ALL 选项，SQL Server 以及 SQLite 支持简写的 EXCEPT。

以下是一个 EXCEPT 运算符的示例：

SELECT id, name
FROM t_set1 
EXCEPT
SELECT id, name
FROM t_set2;

如果使用 Oracle 19c 以及更早的版本，等价的查询语句如下：

-- Oracle
SELECT id, name
FROM t_set1 
MINUS
SELECT id, name
FROM t_set2;

查询返回的结果如下：

id|name 
--|------3|orange

只有“orange”出现在表 t_set1 但不在表 t_set2 中。

差集运算可以改写为等价的左外连接或者右外连接，上面的查询语句可以改写为下面这样：

SELECT t1.id, t1.name
FROM t_set1 t1
LEFT JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name)
WHERE t2.id IS NULL;

其中的 WHERE 条件是关键，它保留了连接结果中 t_set2.id 为空的数据，也就是只在 t_set1 中出现的记录。

集合运算与排序

我们在使用集合运算符时需要注意几个事项，首先就是排序操作。如果我们想要对集合运算的结果进行排序操作，必须将 ORDER BY 子句写在整个查询语句的最后，集合运算符之前的 SELECT 语句中不能出现排序子句。

下面是一个错误的查询示例：

-- 集合运算中的错误排序子句
SELECT id, name
FROM t_set1
ORDER BY id
UNION ALL
SELECT id, name
FROM t_set2;

无论我们使用哪种数据库，以上语句都会返回语法错误。因为在集合运算之前进行排序没有意义，最终结果的返回顺序可能会发生改变。正确的做法是在整个查询语句的最后指定排序操作，例如：

SELECT id, name
FROM t_set1
UNION ALL
SELECT id, name
FROM t_set2
ORDER BY id;

查询返回的结果如下：

id|name 
--|------1|apple 1|apple 2|banana2|banana3|orange4|pear

运算符的优先级

另一个关于集合运算的注意事项就是 3 种集合运算符的优先级。当我们使用集合运算符将多个查询语句进行组合时，需要注意它们之间的优先级和执行顺序：

  • 按照 SQL 标准，交集运算符（INTERSECT）的优先级高于并集运算符（UNION）和差集运算符（EXCEPT）。但是 Oracle 和 SQLite 中所有集合运算符的优先级相同。
  • 相同的集合运算符按照从左至右的顺序执行。
  • 使用括号调整多个集合运算符的执行顺序。

以下示例说明了不同集合运算符的执行优先级：

-- Microsoft SQL Server、PostgreSQL 以及 SQLite
SELECT 1 AS n
UNION ALL
SELECT 1
INTERSECT 
SELECT 1;

以上语句在 Microsoft SQL Server 和 PostgreSQL 中返回的结果如下：

n
-
1
1

查询返回了 2 个重复的 1。因为查询先执行 INTERSECT 运算符，结果包含 1 个 1。然后执行 UNION ALL 运算符，最终的结果保留了重复的 1。

以上语句在 Oracle 和 SQLite 中返回的结果如下：

n
-
1

查询只返回了 1 个 1。因为查询先执行 UNION ALL 运算符，结果包含 2 个 1。然后再执行 INTERSECT 运算符，最终的结果去除了重复值。

以下示例说明了相同集合运算符的执行顺序：

SELECT 1 AS n
UNION ALL
SELECT 1
UNION
SELECT 1;

查询返回的结果如下：

n
-
1

以上语句只返回了 1 个 1，因为第二个 UNION 运算符去除了重复的记录。

如果我们将以上示例中的两个并集运算符交换位置：

SELECT 1 AS n
UNION
SELECT 1
UNION ALL
SELECT 1;

查询返回的结果如下：

n
-
1
1

以上语句返回了 2 个重复的 1，因为第二个 UNION ALL 运算符保留了重复的记录。

最后，我们可以在使用括号来修改多个集合运算符的执行顺序：

-- MySQL、Oracle、Microsoft SQL Server 和 PostgreSQL
SELECT 1 AS n
UNION ALL
(SELECT 1
INTERSECT
SELECT 1);

以上示例先执行括号内的查询语句，因此查询返回的结果如下：

n
-
1
1

SQLite 目前不支持这种修改集合运算符优先级的方式。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/314058.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

springcloud Ribbon的详解

springcloud Ribbon的详解

1、Ribbon是什么 Ribbon是Netflix发布的开源项目，Spring Cloud Ribbon是基于Netflix Ribbon实现的一套客户端负载均衡的框架。 2、Ribbon能干什么 LB负载均衡(Load Balance)是什么？简单的说就是将用户的请求平摊的分配到多个服务上，从而达…
阅读更多...
C++:const成员和取地址操作符

C++:const成员和取地址操作符

目录 一、const成员 二、取地址及const取地址操作符重载 一、const成员 将const修饰的“成员函数”称之为const成员函数，const修饰类成员函数，实际修饰该成员函数 隐含的this指针，表明在该成员函数中不能对类的任何成员进行修改。 注&…
阅读更多...
ESLlint重大更新后，使用旧版ESLint搭配Prettier的配置方式

ESLlint重大更新后，使用旧版ESLint搭配Prettier的配置方式

概要 就在前几天，ESLint迎来了一次重大更新，9.0.0版本，根据官方文档介绍，使用新版的先决条件是Node.js版本必须是18.18.0、20.9.0，或者是>21.1.0的版本，新版ESLint将不再直接支持以下旧版配置(非扁平化…
阅读更多...
【CSS】使用 scroll snap 实现页面的垂直大屏滚动

【CSS】使用 scroll snap 实现页面的垂直大屏滚动

CSS 属性 scroll-snap-type 设置了在有滚动容器的情形下吸附至吸附点的严格程度。 scroll-snap-type 使用 scroll snap 也可以用于垂直滚动&#xff0c;全屏展示就是一个很好的例子: <main><section class"section section-1"></section><sect…
阅读更多...
pytest使用 pytest-rerunfailures 插件实现失败用例重跑功能

pytest使用 pytest-rerunfailures 插件实现失败用例重跑功能

使用 pytest 进行测试时&#xff0c;你可以通过安装并配置 pytest-rerunfailures 插件来实现失败用例重跑功能。以下是一个示例说明&#xff1a; 假设你有一个测试文件 test_example.py 包含如下测试用例&#xff1a; import pytestpytest.mark.parametrize("num",…
阅读更多...
Idea：通义千问插件

Idea：通义千问插件

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 目录 一、通义千问大模型 二、程序编写助手 三、Idea安装通义千问插件 总结 提示&#xff1a;以下是本篇文章正文内容&#xff0c;下面案例可供参考 一、通义千问大模型…
阅读更多...
python 中使用 ESP8266 实现语音识别（或热词检测）

python 中使用 ESP8266 实现语音识别（或热词检测）

介绍 我的大部分家庭自动化都是通过对网络中的设备执行 HTTP 请求来控制的。 （例如：开灯、打开收音机、控制加热系统...... 这可以使用ESP8266轻松完成。我有一个控制器和一个触摸传感器，当我在床上时用它来控制灯光和音乐。 像 Amazon Echo 或 Google Homepod 一样添加语…
阅读更多...
基于harris角点和RANSAC算法的图像拼接matlab仿真

基于harris角点和RANSAC算法的图像拼接matlab仿真

目录 1.算法运行效果图预览 2.算法运行软件版本 3.部分核心程序 4.算法理论概述 5.算法完整程序工程 1.算法运行效果图预览 2.算法运行软件版本 MATLAB2022a 3.部分核心程序 ....................................................................... I1_harris fu…
阅读更多...
某翻译平台翻译接口逆向之webpack学习

某翻译平台翻译接口逆向之webpack学习

逆向网址 aHR0cHM6Ly9mYW55aS55b3VkYW8uY29tLw 逆向链接 aHR0cHM6Ly9mYW55aS55b3VkYW8uY29tLyMv 逆向接口 aHR0cHM6Ly9kaWN0LnlvdWRhby5jb20vd2VidHJhbnNsYXRl 逆向过程 请求方式 POST 逆向参数 sign c168e4cb76169e90f82d28118dbd24d2 接口请求结果解密 过程分析 根据XHR…
阅读更多...
计算机网络—— book

计算机网络—— book

文章目录 一、概述1.1互联网的核心部分1&#xff0e;电路交换的主要特点2&#xff0e;分组交换的主要特点 1.2.计算机网络的性能1&#xff0e;速率2&#xff0e;带宽3&#xff0e;吞吐量4&#xff0e;时延5&#xff0e;利用率 1.3.计算机网络体系结构协议与划分层次具有五层协议…
阅读更多...
用爬虫玩转石墨文档

用爬虫玩转石墨文档

目录 前言 一、什么是爬虫 二、爬虫原理 三、使用Python实现爬虫 1.安装Python库 2.爬取石墨文档内容 3.解析HTML内容 四、实际应用案例 1.批量下载附件 2.统计文档字数 3.自动化更新文档 总结 前言 在当今信息化时代&#xff0c;文档协作平台早已成为我们日常工作…
阅读更多...
H5点击复制功能 兼容安卓、IOS

H5点击复制功能 兼容安卓、IOS

效果图 HTML代码 <div>链接&#xff1a;<span style"color: #FF8A21" click"CopyUrl" id"copyId"> https://blog.csdn.net/qq_51463650?spm1000.2115.3001.5343</span> </div>复制方法 const CopyUrl () > {let …
阅读更多...
QT跨平台读写Excel

QT跨平台读写Excel

QT跨平台读写Excel 背景Excel工具CMakeLists.txt工程目录 背景 开发框架QT&#xff0c;makefile构建工具CMake&#xff0c;编译器MinGW Excel工具 考虑跨平台则不能使用针对微软COM组件的QAxObject来读写Excel&#xff0c;因此使用开源QtXlsx。 这里是将QXlsx当做源码嵌入使…
阅读更多...
如何快速学习盲打键盘的指法

如何快速学习盲打键盘的指法

学习盲打键盘的指法需要一定的时间和练习&#xff0c;但是以下几个方法可以帮助你加快学习的速度&#xff1a; 掌握正确的手位&#xff1a;了解标准的键盘布局以及手指应该放置的位置是学习盲打的第一步。在QWERTY键盘上&#xff0c;你的左手应该放在ASDF键上&#xff0c;右手应…
阅读更多...
Git--基础学习--面向企业--持续更新

Git--基础学习--面向企业--持续更新

一、基础学习 1.1基本命令 //查询基础信息 git config --global --list //选取合适位置创建 mkdir 文件名 //创建文件夹 //全局配置 git config --global user.email "****e***i" git config --global user.name "*** K****"//--------------------进入…
阅读更多...
10.Godot Input与自定义单例的使用

10.Godot Input与自定义单例的使用

单例 单例是一个可以在任何一个脚本中对其进行直接访问的对象&#xff0c;分为内置单例与自定义单例。每个单例都是独一无二的对象。内置单例不是节点&#xff0c;主要成员是各类 Server&#xff0c;开发者可以使用它们直接控制游戏程序的图形与音效等内容。此外&#xff0c;还…
阅读更多...
Lock-It for Mac(应用程序加密工具)

Lock-It for Mac(应用程序加密工具)

OSXBytes Lock-It for Mac是一款功能强大的应用程序加密工具&#xff0c;专为Mac用户设计。该软件具有多种功能&#xff0c;旨在保护用户的隐私和数据安全。 Lock-It for Mac v1.3.0激活版下载 首先&#xff0c;Lock-It for Mac能够完全隐藏应用程序&#xff0c;使其不易被他人…
阅读更多...
CYCLE：学习自我完善代码生成

CYCLE：学习自我完善代码生成

目录 IntriductionOverview of the Approach 预训练的代码语言模型在代码生成方面取得了可喜的性能&#xff0c;并提高了人类开发人员的编程效率。然而&#xff0c;现有的代码 LM 评估通常忽略了它们的 自我求精能力&#xff0c;这些评估仅关注一次性预测的准确性。对于代码 L…
阅读更多...
手拉手安装Kafka2.13发送和消费消息

手拉手安装Kafka2.13发送和消费消息

Kafka是一种高吞吐量的分布式发布订阅消息系统&#xff0c;它可以处理消费者在网站中的所有动作流数据。 Kafka启动方式有Zookeeper和Kraft&#xff0c;两种方式只能选择其中一种启动&#xff0c;不能同时使用。 Kafka下载https://downloads.apache.org/kafka/3.7.0/kafka_2.…
阅读更多...
Java——抽象类和接口的区别

Java——抽象类和接口的区别

抽象类和接口都是 Java 中多态的常见使用方式。都需要重点掌握。同时又要认清两者的区别(重要!!! 常见面试题)。 核心区别&#xff1a;抽象类中可以包含普通方法和普通字段&#xff0c;这样的普通方法和字段可以被子类直接使用(不必重写)&#xff0c;而接口中不能包含普通方法…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023