SQL 集合查询

SQL 子查询,我们介绍了 SQL 中各种形式的子查询,以及与子查询相关的 IN、ALL、ANY/SOME、EXISTS 运算符。本文我们来讨论另一种从多个查询中返回组合结果的方法:集合运算。

SQL 中的集合操作符可以将多个查询的结果组合成一个结果。本篇讨论三种集合操作符:UNION [ALL]、INTERSECT 以及 EXCEPT,同时还演示了它们的执行优先级。有时候,可以利用连接查询实现与集合操作相同的效果。

集合运算

数据库中的表与集合理论中的集合非常类似,表是由行组成的集合。因此, SQL 支持基于行的各种集合操作:并集运算(UNION)、交集运算(INTERSECT)和差集运算(EXCEPT)。它们都用于将两个查询的结果集合并成一个结果集,但是合并的规则各不相同。

需要注意的是,SQL 集合操作中的两个查询结果需要满足以下条件:

  • 结果集中字段的数量和顺序必须相同
  • 结果集中对应字段的类型必须匹配或兼容

也就是说,对于参与运算的两个查询结果,要求它们的字段结构相同。如果一个查询返回 2 个字段,另一个查询返回 3 个字段,肯定无法合并。如果一个查询返回数字类型的字段,另一个查询返回字符类型的字段,通常也无法合并;不过数据库可能会尝试执行隐式的类型转换。

交集求同

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

SQL 集合查询

其中,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 年都是优秀员工的员工编号:

-- Oracle、SQL Server 以及 PostgreSQL 实现
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 不支持交集运算。

以上示例可以改写为等价的连接查询:

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|

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

并集存异

UNION 操作符用于将两个查询结果相加,返回出现在第一个查询结果或者第二个查询结果中的数据。并集运算的示意图如下:

SQL 集合查询

其中,1 和 2 是两个查询结果中都存在的数据;不过它们在最终结果中各出现一次,UNION 操作符排除了查询结果中的重复记录。

以下语句用于查找 2018 年和 2019 年所有的优秀员工:

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
 UNION
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019;

emp_id|
------|
     9|
    11|
    20|

第一个查询返回了 9 和 11,第二个查询返回了 9 和 20,但最终的结果中只有一个 9。

UNION 操作符还支持 ALL 选项,表示保留查询结果中的重复记录:

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
 UNION ALL
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019;

emp_id|
------|
     9|
    11|
     9|
    20|

UNION ALL 保留了两个 9,因为该员工在 2018 年和 2019 年都是优秀员工。

因此,并集运算的完整语法如下:

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

其中,DISTINCT 表示将合并后的结果进行去重操作;ALL 表示保留结果集中的重复记录;默认为 DISTINCT。

通常来说,UNION ALL 不需要进行重复值的排除,性能比 UNION 更好;尤其是数据量比较大的情况下。

上面的 UNION 示例可以改写为等价的全外连接查询:

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT COALESCE(t1.emp_id,t2.emp_id) emp_id
  FROM (SELECT emp_id  
          FROM excellent_emp
         WHERE year = 2018) t1
  FULL JOIN (SELECT emp_id  
               FROM excellent_emp
              WHERE year = 2019) t2
    ON (t1.emp_id = t2.emp_id);

emp_id|
------|
     9|
    11|
    20|

其中,t1 代表了 2018 年的优秀员工,t2 代表了 2019 年的优秀员工; 全外连接返回了 2018 年和 2019 年所有的优秀员工,注意 COALESCE 函数的作用。

差集排他

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

SQL 集合查询

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

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

-- SQL Server 以及 PostgreSQL 实现
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
EXCEPT
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018;

emp_id|
------|
    20|

-- Oracle 实现
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
 MINUS
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018;

EMP_ID|
------|
    20|

查询结果显示,只有 20 号员工是 2019 年新晋的优秀员工。Oracle 使用关键字 MINUS 表示差集运算。

MySQL 不支持差集运算。

差集运算也可以通过左外连接实现:

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 年不是优秀的员工。

在使用集合运算符的时候,还需要注意几个事项;首先是排序操作。

集合操作中的排序

如果要对集合操作的结果进行排序,需要将 ORDER BY 子句写在最后;集合操作符之前的查询语句中不能出现排序操作。以下是一个错误的语法示例:

-- 集合操作中的错误排序示例
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
 ORDER BY emp_id DESC
 UNION
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018;

执行以上语句将会返回一个语法错误。在集合操作之前进行排序并没有实际意义,因为最终结果的顺序可能会发生变化。正确的做法是在整个语句的最后指定排序操作:

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
 UNION
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
 ORDER BY emp_id DESC;

emp_id|
------|
    20|
    11|
     9|

除了 ORDER BY 子句的位置,还有一个常见的问题就是集合操作符的优先级。

集合操作符的优先级

SQL 提供了 3 种集合操作符:UNION [ALL]、INTERSECT 以及 EXCEPT。我们可以通过多个集合操作符将多个查询的结果进行组合。此时,需要注意它们之间的优先级和执行顺序:

  • INTERSECT 的优先级高于 UNION 和 EXCEPT,但是 Oracle 中所有集合操作符的优先级相同;
  • 相同的集合操作符按照从左至右的顺序执行;
  • 使用括号可以明确指定执行的顺序。

以下示例说明了不同集合操作符的执行顺序:

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT 1 FROM department
UNION ALL
SELECT 1 FROM department
INTERSECT 
SELECT 1 FROM department;

该语句在 SQL Server 和 PostgreSQL 中返回了 7 个重复的 1;因为 INTERSECT 先执行,然后 UNION ALL 保留了重复值。该语句在 Oracle 中则返回了 1 个 1;因为 UNION ALL 先执行,然后 INTERSECT 去除了重复值。

以下语句演示了相同集合操作符的执行顺序:

SELECT 1 FROM department
UNION ALL
SELECT 1 FROM department
UNION
SELECT 1 FROM department;

该查询的结果中只有 1 个 1,因为最后的 UNION 去除了重复的数据。与此不同的是,下面的示例返回了 7 个重复的 1:

SELECT 1 FROM department
UNION
SELECT 1 FROM department
UNION ALL
SELECT 1 FROM department;

最后,我们可以使用括号来修改多个集合操作符的执行顺序:

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT 1 FROM department
UNION ALL
(SELECT 1 FROM department
INTERSECT 
SELECT 1 FROM department);

该语句括号内的集合操作先执行,UNION ALL 后执行;因此查询的结果返回了 7 个重复的 1。

赞(2)
未经允许不得转载:极客笔记 » SQL 集合查询
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址