INTERSECT
On this page
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 ...INTERSECTSELECT ...
Arguments
Accepts two or more queries that return data of comparable data types.
Remarks
-
In addition to being able to be used in queries,
INTERSECTcan 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 int_t(a int, b varchar(30));CREATE TABLE int_s(a int, b varchar(30));CREATE TABLE int_t2(a int, b varchar(30));INSERT INTO int_t VALUES(1, "x"), (2, "y"), (3, "z");INSERT INTO int_s VALUES(1, "x"), (2, "y"), (4, "q");INSERT INTO int_t2 VALUES(1, "x"), (8, "y"), (13, "q"), (2, "y");
SELECT * FROM int_tINTERSECTSELECT * FROM int_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 int_t INTERSECT SELECT * FROM int_s) SELECT a, b FROM mycte;
+------+------+
| a | b |
+------+------+
| 1 | x |
| 2 | y |
+------+------+The following example shows how to combine three result sets with INTERSECT:
SELECT * FROM int_tINTERSECTSELECT * FROM int_sINTERSECTSELECT * FROM int_t2;
+------+------+
| a | b |
+------+------+
| 1 | x |
| 2 | y |
+------+------+Related Topics
Last modified: March 1, 2023