BigQuery INTERSECT and EXCEPT: SQL Set Operations Explained

Almost everyone working with SQL has used UNION and UNION ALL. But these are not the only set operations available in SQL. Meet INTERSECT and EXCEPT.

What do they do? As the name suggests they perform the following set operations:

INTERSECT returns the common entries, all elements present both in A and B, so A ∩ B.
EXCEPT returns the elements present in A, but not in B, so A ∖ B

I find them quite useful when doing data validation, although they can be easily replicated with JOINs.

It's worth noting that while BigQuery supports UNION ALL and UNION DISTINCT, we are required to specify DISTINCT for INTERSECT and EXCEPT. At the time of this writing, INTERSECT ALL and EXCEPT ALL are not supported.

Let's see an example of them in action. Say we have the following two inputs:

Input A:

Rowset A

Input B:

Here's what the output of INTERSECT would look like:

And EXCEPT:

Thanks for reading!

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Intersect and Except in BigQuery

Comments

Practical SQL

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

More from this blog