INTERSECT combines the result set of two queries and returns distinct rows that are returned by both queries. When combining two results sets using INTERSECT, the data types must be compatible and the number and order of the columns should match across queries.

Syntax

SELECT ...
INTERSECT
SELECT ...

Arguments

Accepts two or more queries that return data of comparable data types.

Remarks

In addition to being able to be used in queries, INTERSECT can also be used in subqueries and common table expressions (CTEs).

The column names of the returned result set are the same as the column names returned by the query on the left side of the operator.

If using ORDER BY, column names must match those returned by the left-side query.

Examples

The following example demonstrates how to combine two result sets using INTERSECT:

create table t(a int, b varchar(30));

create table s(a int, b varchar(30));

insert into t values (1, "x"), (2, "y"), (3, "z");

insert into s values (1, "x"), (2, "y"), (4, "q");

select * from t
    -> intersect
    -> select * from s;
+------+------+
| a    | b    |
+------+------+
|    1 | x    |
|    2 | y    |
+------+------+
2 rows in set (0.74 sec)

The following example shows how to use INTERSECT with a CTE:

with mycte(a, b) as (select * from t intersect select * from s) select a, b from mycte;
+------+------+
| a    | b    |
+------+------+
|    1 | x    |
|    2 | y    |
+------+------+

The following example shows how to combine three result sets with INTERSECT:

create table t2(a int, b varchar(30));

insert into t2 values (1, "x"), (8, "y"), (13, "q"), (2, "y");

select * from t
    -> intersect
    -> select * from s
    -> intersect
    -> select * from t2;
+------+------+
| a    | b    |
+------+------+
|    1 | x    |
|    2 | y    |
+------+------+

Related Topics

• UNION and UNION ALL
• EXCEPT and MINUS

Was this Article Helpful?

Thanks For Your Feedback!