PostgreSQL EXCEPT 用法与实例

本文介绍了在 PostgreSQL 中如何使用 EXCEPT 运算符对两个结果集做减法。

在 PostgreSQL 中， EXCEPT 是一个集合运算符，它用于从一个集合中减去出现在另外一个集合中的行。

其他的集合操作运算符还有： UNION 和 INTERSECT。

PostgreSQL EXCEPT 语法

要从一个结果集中减去另一个结果集，请按照以下语法使用 EXCEPT 运算符：

SELECT_statement_1
EXCEPT
SELECT_statement_2
[ORDER BY ...];

或者您可以减去多个结果集，如下：

SELECT_statement_1
EXCEPT
SELECT_statement_2
EXCEPT
SELECT_statement_3
EXCEPT
...
[ORDER BY ...];

这里：

  • SELECT_statement_N 都是个独立 SELECT 语句。
  • 参与 EXCEPT 运算的所有的结果集应该具有相同的列，并且列的数据类型和顺序应该相同。
  • ORDER BY 子句用于对最终结果进行排序，它是可选的。

比如，下面的语句：

SELECT generate_series(1, 5)
EXCEPT
SELECT generate_series(3, 6);

 generate_series
-----------------
               1
               2

这里， generate_series() 函数用来生成结果集。

先看一下第一个结果集：

SELECT generate_series(1, 5);

 generate_series
-----------------
               1
               2
               3
               4
               5

再看一下第二个结果集：

SELECT generate_series(3, 6);

 generate_series
-----------------
               3
               4
               5
               6

EXCEPT 运算符从第一个结果集中的所有的行中删除同时位于第二个结果集中的行，并返回了第一个结果集中剩下的行。

PostgreSQL EXCEPT 实例

我们将使用 Sakila 示例数据库 中的表进行演示，请您先在 PostgreSQL 中安装 Sakila 示例数据库。

我们将使用 film 表进行演示。

要从影片表 film 中获取评级为 G 的影片，请使用下面的语句：

SELECT film_id, title, rating, length, rental_rate
FROM film WHERE rating = 'G';

 film_id |           title           | rating | length | rental_rate
---------+---------------------------+--------+--------+-------------
       2 | ACE GOLDFINGER            | G      |     48 |        4.99
       4 | AFFAIR PREJUDICE          | G      |    117 |        2.99
       5 | AFRICAN EGG               | G      |    130 |        2.99
      11 | ALAMO VIDEOTAPE           | G      |    126 |        0.99
      22 | AMISTAD MIDSUMMER         | G      |     85 |        2.99
      25 | ANGELS LIFE               | G      |     74 |        2.99
      26 | ANNIE IDENTITY            | G      |     86 |        0.99
      39 | ARMAGEDDON LOST           | G      |     99 |        0.99
      43 | ATLANTIS CAUSE            | G      |    170 |        2.99
...
     996 | YOUNG LANGUAGE            | G      |    183 |        0.99
(178 rows)

如果您想要从影片表 film 中获取评级为 G 的影片，但是不包括那些影片长度大于 55 分钟的影片，请使用如下带有 EXCEPT 运算符的语句：

SELECT film_id, title, rating, length, rental_rate
FROM film WHERE rating = 'G'
EXCEPT
SELECT film_id, title, rating, length, rental_rate
FROM film WHERE length > 55;

 film_id |        title        | rating | length | rental_rate
---------+---------------------+--------+--------+-------------
     292 | EXCITEMENT EVE      | G      |     51 |        0.99
       2 | ACE GOLDFINGER      | G      |     48 |        4.99
     247 | DOWNHILL ENOUGH     | G      |     47 |        0.99
     430 | HOOK CHARIOTS       | G      |     49 |        0.99
     542 | LUST LOCK           | G      |     52 |        2.99
     497 | KILL BROTHERHOOD    | G      |     54 |        0.99
     402 | HARPER DYING        | G      |     52 |        0.99
     575 | MIDSUMMER GROUNDHOG | G      |     48 |        4.99
     237 | DIVORCE SHINING     | G      |     47 |        2.99
      83 | BLUES INSTINCT      | G      |     50 |        2.99
     862 | SUMMER SCARFACE     | G      |     53 |        0.99
     697 | PRIMARY GLASS       | G      |     53 |        0.99
     794 | SIDE ARK            | G      |     52 |        0.99
(13 rows)

如果您想要从影片表 film 中获取评级为 G 的影片，但是不包括那些影片长度大于 55 分钟的影片，还不包括那些租金大于 2.99 美元的影片，请使用如下带有 EXCEPT 运算符的语句：

SELECT film_id, title, rating, length, rental_rate
FROM film WHERE rating = 'G'
EXCEPT
SELECT film_id, title, rating, length, rental_rate
FROM film WHERE length > 55
EXCEPT
SELECT film_id, title, rating, length, rental_rate
FROM film WHERE rental_rate >= 2.99;

 film_id |      title       | rating | length | rental_rate
---------+------------------+--------+--------+-------------
     292 | EXCITEMENT EVE   | G      |     51 |        0.99
     247 | DOWNHILL ENOUGH  | G      |     47 |        0.99
     430 | HOOK CHARIOTS    | G      |     49 |        0.99
     497 | KILL BROTHERHOOD | G      |     54 |        0.99
     402 | HARPER DYING     | G      |     52 |        0.99
     862 | SUMMER SCARFACE  | G      |     53 |        0.99
     697 | PRIMARY GLASS    | G      |     53 |        0.99
     794 | SIDE ARK         | G      |     52 |        0.99
(8 rows)

请注意，我们在语句的末尾放置了ORDER BY子句以按标题对电影进行排序。

结论

PostgreSQL EXCEPT 是一个集合运算符，它用于从一个集合中减去出现在另外一个集合中的行。 其他的集合操作运算符还有： UNION 和 INTERSECT。