EXCEPT is an operator provided by Postgres that compares the result sets of two queries and retrieves all the rows that exist in the result set of the first select query but not in the result set of the second SELECT query.

Through practical examples, this write-up will teach you the basic syntax and usage of the Postgres EXCEPT operator. So, let’s start.

How to Use an EXCEPT Operator in PostgreSQL?

To implement the EXCEPT operator, you have to follow the below syntax:

SELECT col_list
FROM tbl_1
[WHERE condition_tbl_1] 
EXCEPT
SELECT col_list
FROM tbl_2
[WHERE condition_tbl_2];

Let’s comprehend this syntax step-by-step:

• In place of col_list, the user can specify the columns/expressions he wants to compare between the two SELECT commands.
• tbl_1 and tbl_2 are the targeted tables.
• WHERE is an optional clause that can be used to specify criteria.

Practical Implementation of the EXCEPT Operator

We have already created a couple of tables in our database named article_details and recommended_articles. Let’s fetch the details of each table one by one:

SELECT * FROM article_details;

Now let’s fetch the content of the recommended_articles table:

SELECT * FROM recommended_articles;

Now we will see how the EXCEPT operator works on the given tables.

Example#1: How to Use the EXCEPT Operator on a Single Column?

The below-given query will show the usage of the EXCEPT operator:

SELECT article_title
FROM article_details
EXCEPT
SELECT article_title
FROM recommended_articles;

The EXCEPT operator retrieves all the article titles of the result set except two titles, i.e., PostgreSQL INSERT Query and PostgreSQL ALTER Command.

Example#2: How to Use the EXCEPT Operator on Multiple Columns?

The below-given query will show you how to use the EXCEPT operator with multiple columns:

SELECT article_id, article_title
FROM article_details
EXCEPT
SELECT article_id, article_title
FROM recommended_articles;

The EXCEPT operator retrieves all the article ids and titles of the first result set except two, namely PostgreSQL INSERT Query having id 7 and PostgreSQL ALTER Command having id 8.

Example#3: How to Use the EXCEPT Operator With ORDER BY Clause?

From the previous result sets, it can be seen that the records are out of order. To sort a result set into a specific order, run the below query:

SELECT *
FROM article_details
EXCEPT
SELECT *
FROM recommended_articles
ORDER BY article_id DESC;

EXCEPT returns all records from the first query's result set that do not appear in the second query's result set.

That was all the basic information related to the Postgres EXCEPT operator.

Conclusion

In PostgreSQL, the EXCEPT operator compares the result sets of two queries and retrieves all the rows that exist in the result set of the first select query but not in the result set of the second SELECT query. Through Practical examples, this post explained the usage of the EXCEPT operator.