Db2 INTERSECT

Summary: in this tutorial, you will learn how to use the Db2 INTERSECT to combine two or more result sets and return only rows that exist in all result sets.

Introduction to Db2 INTERSECT operator

The Db2 INTERSECT combines two or more result sets of subselects and returns only distinct rows that exist in all result sets.

Here is the syntax of the Db2 INTERSECT operator:

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

Like the UNION operator, the subselects above must follow these rules:

• The number and order of columns must be the same in all subselects.
• The data type of the columns (or expressions) in the select list of the subselects must be the same or at least compatible.

The following picture illustrates the INTERSECT operation of two result T1(A, B, C) and T2(B, C, D). The intersection of T1 and T2 result sets returns the distinct rows which are B and C:

Db2 INTERSECT example

We will use the customers and contacts table created in the join tutorial for the demonstration:

This example uses the INTERSECT operator to find the contacts who are also the customers:

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

Here is the output:

In this tutorial, you have learned how to use the Db2 INTERSECT operator to return rows that exist in all input result sets.