INTERSECT operator in BigQuery

The INTERSECT operator in BigQuery is used to return only the rows that are common between the result sets of two or more SELECT queries.

In other words, it returns the overlapping records that appear in all queries involved in the INTERSECT operation.

Key Rules for INTERSECT

Each SELECT statement must return the same number of columns.
Corresponding columns must have compatible data types.
The column order must be the same in all SELECT statements.

SQL Syntax

SELECT column1, column2, ...
FROM `project.dataset.table1`
WHERE condition

INTERSECT DISTINCT

SELECT column1, column2, ...
FROM `project.dataset.table2`
WHERE condition;

A simplified form of the INTERSECT syntax is shown below:

SQL

SELECT column1, column2, ..., columnN
FROM table_name1
[WHERE conditions]

INTERSECT

SELECT column1, column2, ..., columnN
FROM table_name2
[WHERE conditions];

Example: Find common customers in two regions.

SQL

SELECT customer_id
FROM `project.dataset.sales_india`

INTERSECT

SELECT customer_id
FROM `project.dataset.sales_usa`;

Result

Returns customer IDs present in both India and USA sales tables.
Duplicate customer IDs are removed automatically.

Previous Next