Sign inTry Free

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 ...
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 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_t
  INTERSECT
  SELECT * 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_t
  INTERSECT
  SELECT * FROM int_s
  INTERSECT
  SELECT * FROM int_t2;
+------+------+
| a    | b    |
+------+------+
|    1 | x    |
|    2 | y    |
+------+------+

Related Topics

Last modified: March 1, 2023

Was this article helpful?

On this page

Was this article helpful?