PostgreSQL – EXCEPT Operator

In PostgreSQL, the EXCEPT operator is a powerful tool used to return distinct rows from the first query that are not present in the output of the second query. This operator is useful when you need to compare result sets of two or more queries and find the differences.

Let us better understand the EXCEPT Operator in PostgreSQL from this article.

Syntax

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

Rules for Using the EXCEPT Operator

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

• Column Count and Order: The number of columns and their order must be the same in both queries.
• Data Type Compatibility: The data types of the respective columns must be compatible.

Venn Diagram of EXCEPT operator

The Venn diagram below illustrates the result of the EXCEPT operator. The left circle represents the result set of the first query, and the shaded area outside the right circle represents the rows that are in the first query but not in the second.

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.

PostgreSQL EXCEPT Operator Examples

Let us take a look at some of the examples of EXCEPT Operator in PostgreSQL to better understand the concept.

Example 1: Finding Films Not in Inventory

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.

Query:

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:

Explanation: This query returns a list of films that are not present in the inventory by comparing the ‘film_id' from both the ‘film' and ‘inventory' tables.

Example 2: Finding Films Only in English

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:

Explanation: This query returns a list of films that are in the English language by comparing the ‘language_id' and ‘title' from the ‘film' and ‘language' tables.

Important Points About PostgreSQL EXCEPT Operator

• PostgreSQL supports ‘EXCEPT ALL', which returns all rows from the left query including duplicates that are not in the right query. This is different from ‘EXCEPT', which removes duplicates.
• The EXCEPT operator is case–sensitive. Rows with the same values but different cases (e.g., “ABC” vs. “abc”) are considered different.
• The EXCEPT operator can be combined with other set operators like ‘UNION' and ‘INTERSECT' to perform complex data retrieval operations.
• The columns being compared in the EXCEPT queries must have compatible data types. Mismatched data types will result in an error.

R

RajuKumar19

Follow

Improve

Article Tags :

• PostgreSQL
• Python
• postgreSQL-operators

Practice Tags :

• python