In PostgreSQL, the EXCEPT operator returns rows that are returned by the left input query that aren’t returned by the right input query. This can also be referred to as the difference between two queries.

Syntax

The syntax goes like this:

query1 EXCEPT [ALL] query2

Duplicates are eliminated unless EXCEPT ALL is used.

Example

Suppose we have the following tables:

SELECT * FROM Teachers;
SELECT * FROM Students;

Result:

 teacherid | teachername 
-----------+-------------
         1 | Warren
         2 | Ben
         3 | Cathy
         4 | Cathy
         5 | Bill
         6 | Bill

 studentid | studentname 
-----------+-------------
         1 | Faye
         2 | Jet
         3 | Spike
         4 | Ein
         5 | Warren
         6 | Bill

We can use the EXCEPT operator to return teachers that aren’t also students:

SELECT TeacherName FROM Teachers
EXCEPT
SELECT StudentName FROM Students;

Result:

 teachername 
-------------
 Cathy
 Ben

So we only get values that appear in the Teachers table that don’t also appear in the Students table.

By default, the EXCEPT operator returns distinct rows. So in our example, only one row is returned for Cathy, even though there are two teachers with that name.

The above example is the equivalent of the following query:

SELECT TeacherName FROM Teachers
EXCEPT DISTINCT
SELECT StudentName FROM Students;

Result:

 teachername 
-------------
 Cathy
 Ben

This is the same result that we got when there was no explicit DISTINCT keyword. We can include duplicates with the ALL keyword (more on this later).

We can get different results, depending on which table is on the left and which is on the right. Here’s an example that puts the Students table on the left and Teachers on the right:

SELECT StudentName FROM Students
EXCEPT
SELECT TeacherName FROM Teachers;

Result:

 studentname 
-------------
 Faye
 Jet
 Spike
 Ein

This time we get students that aren’t also teachers.

Include Duplicates

By default, the EXCEPT operator implicitly applies a DISTINCT operation. In other words, it returns only distinct values by default.

We can include the ALL keyword in order to include duplicates in our result:

SELECT TeacherName FROM Teachers
EXCEPT ALL
SELECT StudentName FROM Students;

Result:

 teachername 
-------------
 Cathy
 Cathy
 Bill
 Ben

This time we got four rows, instead of the two that we got in our first example.

We can see that both Cathys were returned instead of just one like in our first example.

As for Bill? There are two Bills in the Teachers table, but only one is returned here. That’s probably because there’s one Bill in the Students table, which would exclude one of the Bills from our results.

An Alternative

It’s possible to get the same result without using the EXCEPT operator. For example, we could rewrite our first example to this:

SELECT 
    DISTINCT TeacherName
FROM Teachers t 
WHERE NOT EXISTS (SELECT StudentName FROM Students s
WHERE t.TeacherName = s.StudentName);

Result:

 teachername 
-------------
 Cathy
 Ben