In PostgreSQL, the EXCEPT operator is used to return distinct rows from the first (left) query that are not in the output of the second (right) query while comparing result sets of two or more queries.
Syntax: SELECT column_list FROM A WHERE condition_a EXCEPT SELECT column_list FROM B WHERE condition_b;
The below rules must be obeyed while using the EXCEPT operator:
- 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 below Venn diagram illustrates the result of EXCEPT operator:
For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.
Here we will query for films that are not in the inventory using EXCEPT operator from data of the “film” and “inventory” tables of our sample database and sort them using ORDER BY clause based on the film title.
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;
Here we will query for films that are only in the English Language (ie, language_id = 1) using EXCEPT operator from data of the “film” and “language” tables of our sample database and sort them using the ORDER BY clause based on the film title.
SELECT language_id, title FROM film WHERE language_id = 1 EXCEPT SELECT DISTINCT language.language_id, name FROM language INNER JOIN film ON film.language_id = language.language_id ORDER BY title;;
- PostgreSQL - SOME Operator
- PostgreSQL - NOT LIKE operator
- PostgreSQL - ALL Operator
- PostgreSQL - NOT IN operator
- PostgreSQL - ANY Operator
- PostgreSQL - NOT BETWEEN operator
- PostgreSQL - BETWEEN operator
- PostgreSQL - LIKE operator
- PostgreSQL - IN operator
- PostgreSQL - ILIKE operator
- PostgreSQL - EXISTS Operator
- PostgreSQL - IS NULL operator
- PostgreSQL - UNION operator
- PostgreSQL - INTERSECT Operator
- PostgreSQL - CTE
- PostgreSQL - DELETE
- PostgreSQL - ROLLUP
- PostgreSQL - SELECT INTO
- PostgreSQL - MIN() Function
- PostgreSQL - INNER JOIN
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.