PostgreSQL EXCEPT
Summary: in this tutorial, you will learn how to use the PostgreSQLEXCEPT operator to return a result set containing the rows in the first query that does not appear in the output of the second query.
Introduction to the PostgreSQL EXCEPT operator
Like theUNION andINTERSECT operators, theEXCEPT operator returns rows by comparing the result sets of two or more queries.
TheEXCEPT operator returns distinct rows from the first (left) query that are not in the second (right) query.
The following illustrates the syntax of theEXCEPT operator.
SELECT select_listFROM AEXCEPTSELECT select_listFROM B;The queries that involve theEXCEPT need to follow these 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 theEXCEPT operator:
If you want to sort the rows in the combined result sets, you need to place theORDER BY clause after the second query:
SELECT select_listFROM AEXCEPTSELECT select_listFROM BORDER BY sort_expression;Setting up sample tables
We’ll create thetop_rated_films and most_popular_films tables for demonstration:
CREATE TABLE top_rated_films( titleVARCHAR NOT NULL, release_yearSMALLINT);CREATE TABLE most_popular_films( titleVARCHAR NOT NULL, release_yearSMALLINT);INSERT INTO top_rated_films(title, release_year)VALUES ('The Shawshank Redemption',1994), ('The Godfather',1972), ('The Dark Knight',2008), ('12 Angry Men',1957);INSERT INTO most_popular_films(title, release_year)VALUES ('An American Pickle',2020), ('The Godfather',1972), ('The Dark Knight',2008), ('Greyhound',2020);SELECT * FROM top_rated_films;SELECT * FROM most_popular_films;The contents of thetop_rated_films table:
title | release_year--------------------------+-------------- The Shawshank Redemption | 1994 The Godfather | 1972 The Dark Knight | 2008 12 Angry Men | 1957(4 rows)The contents of themost_popular_films table:
title | release_year--------------------+-------------- An American Pickle | 2020 The Godfather | 1972 The Dark Knight | 2008 Greyhound | 2020(4 rows)PostgreSQL EXCEPT operator examples
Let’s take some examples of using theEXCEPT operator
1) Basic EXCEPT operator example
The following statement uses theEXCEPT operator to find the top-rated films that are not popular:
SELECT * FROM top_rated_filmsEXCEPTSELECT * FROM most_popular_films;Output:
title | release_year--------------------------+-------------- The Shawshank Redemption | 1994 12 Angry Men | 1957(2 rows)2) Using the EXCEPT operator with the ORDER BY clause
The following statement uses theORDER BY clause in the query to sort the result set returned by theEXCEPT operator by titles:
SELECT * FROM top_rated_filmsEXCEPTSELECT * FROM most_popular_filmsORDER BY title;Output:
title | release_year--------------------------+-------------- 12 Angry Men | 1957 The Shawshank Redemption | 1994(2 rows)Notice that we placed theORDER BY clause after the second query to sort the films by titles.
Summary
- Use the PostgreSQL
EXCEPToperator to combine rows from two result sets and return a result set containing rows from the first result set that do not appear in the second result set.
Last updated on