Home » PostgreSQL Tutorial » PostgreSQL EXCEPT Operator

PostgreSQL EXCEPT Operator

Summary: in this tutorial, you’ll learn how to use the PostgreSQL EXCEPT operator to combine result sets of two queries and return the records from the first query that are not in the second.

Getting Started with the PostgreSQL EXCEPT Operator #

The EXCEPT operator allows you to combine two queries and find the difference between the result sets.

The EXCEPT operator returns the rows from the first result set that are not present in the second.

Here’s the syntax of the EXCEPT operator:

SELECT column1, column2
FROM tableA
EXCEPT
SELECT column1, column2
FROM tableB;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

  • First, specify two SELECT statements you want to combine with their result sets.
  • Second, use the EXCEPT operator between the SELECT statements to find the difference between the result set of the queries.

To make the EXCEPT operator valid, the SELECT statements must adhere to the following rules:

  • They must return result sets that have the same number of columns.
  • The data types of the corresponding columns must be compatible.

The EXCEPT operator removes the duplicate rows from the final result sets.