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):

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:

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.