PostgreSQL EXCEPT statement

Preamble

The PostgreSQL EXCEPT statement is used to return all lines in the first SELECT statement that are not returned by the second SELECT statement.

Each SELECT operator will define the data set.

EXCEPT will extract all records from the first dataset and then delete all records from the second dataset from the results.

EXCEPT query

Explanation: The “EXCEPT” query will return the records in the shaded blue area. These are records that exist in the SELECT 1 data set, not in the SELECT 2 data set.

Each SELECT operator in an “EXCEPT” query must have the same number of fields in the result sets with the same data types.

The syntax for EXCEPT in PostgreSQL

SELECT expression1_id, expression2_id, ... expression_n_id
FROM tables
[WHERE conditions]
EXCEPT
SELECT expression1_id, expression2_id, ... expression_n_id
FROM tabs
[WHERE conds];

Parameters and arguments of the statement

• expressions – Columns or calculations that you want to compare between two SELECT operators. They do not necessarily have to be the same fields in each SELECT statement, but the corresponding columns must be of the same data type.
• Tables – The tables from which you want to get the records. At least one table must be specified in the FROM operator.
• WHERE conds – Optional. The conditions to be met for the records to be selected.

Note:

• Both SELECT operators must have the same number of expressions.
• The corresponding columns in each of the SELECT operators must have similar data types.
• The EXCEPT operator shall return all records from the first SELECT operator that is not in the second SELECT operator.
• The EXCEPT operator in PostgreSQL is equivalent to the MINUS operator in Oracle.

Example of an EXCEPT operator with one expression

Let’s consider an example of an EXCEPT operator in PostgreSQL, which returns a single field with the same data type.
For example:

SELECT category_id
FROM products
EXCEPT
SELECT category_id
FROM inventory;

In this EXCEPT example, all category_id values that are in the products table are returned instead of the inventory table. This means that if the category_id value existed in the products table as well as the inventory table, the category_id value will not be displayed in the EXCEPT query results.

Example of an operator with multiple expressions

Next, let’s look at an example of an EXCEPT query in PostgreSQL that returns more than one column.
For example:

SELECT contact_id,
last_name,
first_name
FROM contacts
WHERE last_name = 'Ivanov'
EXCEPT
SELECT customer_id,
last_name,
first_name
FROM customers
WHERE customer_id < 99;

In this example, the EXCEPT query will return records from the contacts table with contact_id, last_name, and first_name a value that does not match the customer_id, last_name, and first_name in the customer’s table.
Example of an operator using ORDER BY

How to use the ORDER BY statement in an EXCEPT query in PostgreSQL

For example:

SELECT supplier_id,
supplier_name
FROM suppliers
WHERE supplier_id >= 59
EXCEPT
SELECT company_id,
company_name
FROM companies
WHERE state = 'Nevada'
ORDER BY 2;

In this EXCEPT example, since column names in two SELECT operators are different, it is more advantageous to refer to columns in ORDER BY operator by their position in the resulting set. In this example, we have sorted the results by supplier_name / company_name in ascending order as ORDER BY 2.

The fields supplier_name / company_name are at position #2 in the resulting set.

SQL UNION, INTERSECT, EXCEPT

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.