# INTERSECT

The `INTERSECT` command 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

```sql
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`:

```sql
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");

```

```sql
SELECT * FROM int_t
  INTERSECT
  SELECT * FROM int_s;

```

```output

+------+------+
| a    | b    |
+------+------+
|    1 | x    |
|    2 | y    |
+------+------+
2 rows in set (0.74 sec)
```

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

```sql
WITH mycte(a, b) AS (SELECT * FROM int_t INTERSECT SELECT * FROM int_s) SELECT a, b FROM mycte;

```

```output

+------+------+
| a    | b    |
+------+------+
|    1 | x    |
|    2 | y    |
+------+------+

```

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

```sql
SELECT * FROM int_t
  INTERSECT
  SELECT * FROM int_s
  INTERSECT
  SELECT * FROM int_t2;

```

```output

+------+------+
| a    | b    |
+------+------+
|    1 | x    |
|    2 | y    |
+------+------+

```

## Related Topics

* [UNION and UNION ALL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/union.md)
* [EXCEPT and MINUS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/except-and-minus.md)

***

Modified at: June 12, 2026

Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/intersect/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/intersect/)

(An index of the documentation is available at /llms.txt)