Db2 EXCEPT

Summary: in this tutorial, you will learn how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.

Db2 EXCEPT Operator Overview

The Db2 EXCEPT combines two or more result sets of subselects. It returns the result set of the first subselect minus any matching rows of the result of the second subselect.

Here is the syntax of the Db2 EXCEPT operator:

subselect_1 EXCEPT subselect_2
Code language: SQL (Structured Query Language) (sql)

The columns and expression in the select list of the subselects must follow these rules:

  • The number and order of columns or expressions must be the same in both subselects.
  • The data types of the corresponding columns or expressions must be the same or compatible.

The following picture illustrates the EXCEPT operation of the two result sets T1 (A, B, C) and T2 (B, C, D):

Db2 EXCEPT

The except of T1 and T2 returns A which is the distinct rows from the T1 result set that does not appear in the T2 result set.

Db2 EXCEPT example

We’ll use the customers and contacts tables created in the join tutorial for the demonstration:

db2 join customers table
db2 join contacts table

The following example uses the EXCEPT operator to find the customers who are not in the contacts table:

SELECT name FROM customers EXCEPT SELECT name FROM contacts;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

NAME ----------- Jessica Lily

In this tutorial, you have learned how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.

Was this tutorial helpful ?