August 3, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL EXCEPT
operator to return the rows in the first query that do not appear in the output of the second query.
Table of Contents
Introduction to the PostgreSQL EXCEPT operator
Like the UNION and INTERSECT operators, the EXCEPT
operator returns rows by comparing the result sets of two or more queries.
The EXCEPT
operator returns distinct rows from the first (left) query that are not in the output of the second (right) query.
The following illustrates the syntax of the EXCEPT
operator.
SELECT select_list
FROM A
EXCEPT
SELECT select_list
FROM B;
The queries that involve in the EXCEPT
need to follow these rules:
- 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 following Venn diagram illustrates the EXCEPT
operator:
PostgreSQL EXCEPT operator examples
We’ll use the top_rated_films
and most_popular_films
tables created in the UNION tutorial:
The top_rated_films
table:
The most_popular_films
table:
The following statement uses the EXCEPT
operator to find the top-rated films that are not popular:
SELECT * FROM top_rated_films
EXCEPT
SELECT * FROM most_popular_films;
The following statement uses the ORDER BY
clause in the query to sort result sets returned by the EXCEPT
operator:
SELECT * FROM top_rated_films
EXCEPT
SELECT * FROM most_popular_films
ORDER BY title;
Notice that we placed the ORDER BY clause at the end of the statement to sort films by title.
Summary
- Use the PostgreSQL
EXCEPT
operator to get the rows from the first query that do not appear in the result set of the second query.