PostgreSQL EXCEPT

This article describes how to use the EXCEPT operator get a difference from two sets.

In PostgreSQL, EXCEPT is a set operator that returns rows that are in the first set but not in the second set.

Other set operators are: UNION and INTERSECT.

PostgreSQL EXCEPT syntax

To subtract one result set from another, use the EXCEPT operator according to the following syntax:

SELECT_statement_1
EXCEPT
SELECT_statement_2
[ORDER BY ...];

Or you can subtract multiple result sets like this:

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

Here:

  • The SELECT_statement_N are independent SELECT statements.
  • All result sets participating in the EXCEPT operation should have the same columns, and the data types and order of the columns should be the same.
  • The ORDER BY clause is used to sort the final result and it is optional.

For example, the following statement:

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

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

Here, the generate_series() function is used to generate the result set.

Let’s take a look at the first result set:

SELECT generate_series(1, 5);

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

And take a look at the second result set:

SELECT generate_series(3, 6);

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

The EXCEPT operator removes all rows that are in the second result set from the first result set, and returns the remaining rows in the first result set.

PostgreSQL EXCEPT Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

We will use film tables for demonstration.

To get films rated G from the film table, use the following statement:

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)

If you want to get films rated G, but not those that are longer than 55 minutes, use the following statement with the EXCEPT operator:

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)

If you want to get films rated G, but not those that are greater than 55 minutes in length, and those whose rent is greater than $2.99, use the following statement with the EXCEPT operator:

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)

Note that we put an ORDER BY clause at the end of the statement to sort the films by title.

Conclusion

PostgreSQL EXCEPT is a set operator that returns rows that are in the first set but not in the second set. Other set operators are: UNION and INTERSECT.