PostgreSQL EXCEPT Operator (original) (raw)

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.

**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:

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:

Venn Diagram 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:

PostgreSQL - EXCEPT Operator

*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