Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-except-operator/ below:

PostgreSQL - EXCEPT Operator - GeeksforGeeks

Last Updated : 12 Jul, 2025

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.

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:

• 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.

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.

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

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.

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.

• 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4