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 quires.
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.
1 2 3 4 5 6 7 | SELECT column_list FROM A WHERE condition_a EXCEPT SELECT column_list FROM B WHERE condition_b; |
To combine the queries using the EXCEPT
operator, you must obey the following 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 result of the of EXCEPT
operator that apply to the A and B tables.
PostgreSQL EXCEPT example
Let’s take a look at the film
and inventory
tables of the sample database.
The following query returns the films in the film
table.
1 2 3 4 5 6 7 | SELECT film_id, title FROM film ORDER BY title; |
The following query returns the films that are in the inventory:
1 2 3 4 5 6 7 | SELECT distinct inventory.film_id, title FROM inventory INNER JOIN film ON film.film_id = inventory.film_id ORDER BY title; |
Both queries return a result set that consists of two columns: film_id
and title
.
To get the films that are not in the inventory, you use the EXCEPT
operator as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT film_id, title FROM film EXCEPT SELECT DISTINCT inventory.film_id, title FROM inventory INNER JOIN film ON film.film_id = inventory.film_id ORDER BY title; |
Notice that we placed the ORDER BY clause at the end of the statement to sort the films by their titles. If you place the ORDER BY
clause in each query, the final result may not be sorted because each query will sort the result set by the title
column and after that the EXCEPT
operator is applied to the both queries.
In this tutorial, we have shown you how to use the PostgreSQL EXCEPT operator to combine two queries to get the rows in the first query that do not appear in the output of the second query.