EXCEPT operator in BigQuery

The EXCEPT operator in BigQuery is a set operator used to return rows that exist in the first SELECT query but not in the second SELECT query.

In simple terms, it performs a set difference operation:

Result = First query − Second query

Key Rules for EXCEPT

Each SELECT statement used with EXCEPT must:

• Return the same number of columns
• Have compatible data types in corresponding columns
• Maintain the same column order

SQL Syntax

SELECT column1, column2, ...
FROM table1
WHERE condition

EXCEPT

SELECT column1, column2, ...
FROM table2
WHERE condition;    

Example: Find customers present only in India sales.

SQL

SELECT customer_id
FROM `project.dataset.sales_india`

EXCEPT

SELECT customer_id
FROM `project.dataset.sales_usa`;    

Result

• Returns customer IDs that exist in India sales but not in USA sales
• Duplicate values are automatically removed