Open In App

PostgreSQL – EXCEPT Operator

Improve
Improve
Like Article
Like
Save
Share
Report

In PostgreSQL, the EXCEPT operator is used to return distinct rows from the first (left) query that are not in the output of the second (right) query while comparing result sets of two or more queries.

Syntax:
SELECT column_list
FROM A
WHERE condition_a
EXCEPT 
SELECT column_list
FROM B
WHERE condition_b;

The below rules must be obeyed while using the EXCEPT operator:

  • The number of columns and their orders must be the same in the two queries.
  • The data types of the respective columns must be compatible.

The below Venn diagram illustrates the result of EXCEPT operator:

For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.

Example 1:

Here we will query for films that are not in the inventory using EXCEPT operator from data of the “film” and “inventory” tables of our sample database and sort them using ORDER BY clause based on the film title.

SELECT
    film_id,
    title
FROM
    film
EXCEPT
    SELECT
        DISTINCT inventory.film_id,
        title
    FROM
        inventory
    INNER JOIN film ON film.film_id = inventory.film_id
ORDER BY title;

Output:

Example 2:
Here we will query for films that are only in the English Language (ie, language_id = 1) using EXCEPT operator from data of the “film” and “language” tables of our sample database and sort them using the ORDER BY clause based on the film title.

SELECT
    language_id,
    title
FROM
    film
WHERE
        language_id = 1
EXCEPT
    SELECT
        DISTINCT language.language_id,
        name
    FROM
        language
    INNER JOIN film ON film.language_id = language.language_id
ORDER BY title;;

Output:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads