INTERSECT Operator

When two queries are combined by this set operator, INTERSECT calculates the set intersection of the rows returned by the two queries that are its operands.

The rows that INTERSECT returns are present in the results sets of both the left and the right SELECT statements. The INTERSECT results are always distinct or unique rows, because INTERSECT eliminates any duplicate rows.

Consider the following example, where the table t1 has following rows:

create table t1 (col1 int);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (2);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (4);
insert into t1 values (NULL);
insert into t1 values (NULL);
insert into t1 values (NULL);

In the same example, table t2 has these rows:

create table t2 (col1 int);
insert into t2 values (1);
insert into t2 values (3);
insert into t2 values (4);
insert into t2 values (4);
insert into t2 values (NULL);

The following query returns the distinct rows from both the query on the left and right sides of the INTERSECT operand. The important thing to be noted here is the result has a NULL value. Because the NULL value in table t2 is considered to be equal when table t2 is compared to table t1, so a NULL value from the set intersection is returned in the combined result set:

SELECT col1 FROM t1 INTERSECT SELECT col1 FROM t2;

       col1


          1
          3
          4
4 row(s) retrieved.

The INTERSECT operator has some (but not all) of the same restrictions as the UNION operator, but INTERSECT does not support the ALL keyword that enables UNION to return duplicate values. See also the topic Restrictions on a Combined SELECT.