PostgreSQL EXCEPT Operator

 PostgreSQL EXCEPT Operator

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.

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 the 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 a 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.