---
title: "INTERSECT"
description: "INTERSECT returns only the rows that appear in the result sets of both of two queries, effectively computing the common rows between them."
canonical: "https://motherduck.com/glossary/intersect/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "Running dual execution (or hybrid) queries | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/running-hybrid-queries/"
  - title: "DuckLake: The Definitive Guide — Live Author Q&A with Matt Martin & Alex Monahan | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-definitive-guide-oreilly-book/"
---

# INTERSECT

> INTERSECT returns only the rows that appear in the result sets of both of two queries, effectively computing the common rows between them.

## Overview
`INTERSECT` is a set operation that returns rows present in both of two `SELECT` queries' result sets. Like `UNION`, the two queries must return the same number of columns with compatible types, and column names in the output come from the first query.

```sql
SELECT customer_id FROM web_signups
INTERSECT
SELECT customer_id FROM email_subscribers;
-- customers who both signed up on the web and subscribed to email
```

## INTERSECT vs INNER JOIN
`INTERSECT` compares entire rows (or the full set of selected columns) for equality, whereas a `JOIN` matches on specific key columns and can return additional columns from both sides. For a simple "which IDs appear in both lists" check, `INTERSECT` is often more readable than a `JOIN`; for combining columns from both sides, a `JOIN` is the right tool.

## Duplicate handling: INTERSECT vs INTERSECT ALL
Standard `INTERSECT` uses set semantics -- duplicates are eliminated, and each distinct row appears at most once in the output, regardless of how many times it appeared in either input. DuckDB also supports `INTERSECT ALL`, which uses bag semantics: a row appears in the output the minimum number of times it occurred across both inputs.

```sql
-- table_a has 'x' three times, table_b has 'x' twice
SELECT val FROM table_a
INTERSECT ALL
SELECT val FROM table_b;
-- returns 'x' twice
```

Many mainstream databases only implement plain `INTERSECT`; DuckDB's support for the `ALL` variant follows the full ANSI SQL set-operation spec.

## NULL handling
`INTERSECT` treats two `NULL`s as equal for the purpose of matching rows (unlike `=`, which treats `NULL = NULL` as unknown), so a `NULL` value present in both inputs is included in the intersection.