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

Db2 INTERSECT example

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

db2 join customers table
db2 join contacts table

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:

Db2 INTERSECT example

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

Was this tutorial helpful ?