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:

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.