Intersect and Except in BigQuery
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:
WITH input_a AS (
SELECT 1 AS customer_id, 1001 AS order_id
UNION ALL
SELECT 2 AS customer_id, 1002 AS order_id
UNION ALL
SELECT 3 AS customer_id, 1003 AS order_id
),
input_b AS (
SELECT 1 AS customer_id, 1001 AS order_id
UNION ALL
SELECT 5 AS customer_id, 1010 AS order_id
UNION ALL
SELECT 6 AS customer_id, 1012 AS order_id
)
SELECT customer_id, order_id from input_b
INTERSECT DISTINCT
SELECT customer_id, order_id FROM input_a
And EXCEPT:
WITH input_a AS (
SELECT 1 AS customer_id, 1001 AS order_id
UNION ALL
SELECT 2 AS customer_id, 1002 AS order_id
UNION ALL
SELECT 3 AS customer_id, 1003 AS order_id
),
input_b AS (
SELECT 1 AS customer_id, 1001 AS order_id
UNION ALL
SELECT 5 AS customer_id, 1010 AS order_id
UNION ALL
SELECT 6 AS customer_id, 1012 AS order_id
)
SELECT customer_id, order_id from input_b
EXCEPT DISTINCT
SELECT customer_id, order_id FROM input_a
Thanks for reading!