Scribd
Upload
44 views2 pages

Introduction To The PostgreSQL EXCEPT Operator

The document discusses the PostgreSQL EXCEPT operator which returns distinct rows from the first query that are not in the output of the second query. It provides examples of using the EXCEPT operator to find top-rated films that are not popular and sorts the results.

Uploaded by

Lord_King
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
44 views2 pages

Introduction To The PostgreSQL EXCEPT Operator

The document discusses the PostgreSQL EXCEPT operator which returns distinct rows from the first query that are not in the output of the second query. It provides examples of using the EXCEPT operator to find top-rated films that are not popular and sorts the results.

Uploaded by

Lord_King
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

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 queries.

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.

SELECT select_list
FROM A
EXCEPT
SELECT select_list
FROM B;
Code language: SQL (Structured Query Language) (sql)

The queries that involve in the EXCEPT 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 the EXCEPT operator:

PostgreSQL EXCEPT operator examples


We’ll use the top_rated_films and most_popular_films tables created in
the UNION tutorial:

The top_rated_films table:
The most_popular_films table:

The following statement uses the EXCEPT operator to find the top-rated films that are not
popular:

SELECT * FROM top_rated_films


EXCEPT
SELECT * FROM most_popular_films;
Code language: SQL (Structured Query Language) (sql)

The following statement uses the ORDER BY clause in the query to sort result sets
returned by the EXCEPT operator:

SELECT * FROM top_rated_films


EXCEPT
SELECT * FROM most_popular_films
ORDER BY title;
Code language: SQL (Structured Query Language) (sql)

Notice that we placed the ORDER BY clause at the end of the statement to sort films by
title.

Summary
 Use the PostgreSQL EXCEPT operator to get the rows from the first query that do
not appear in the result set of the second query.

You might also like