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 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
subselect_1 INTERSECT subselect_2
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:
We will use the
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;
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.