JA EN
shiichan

R2 SQL now speaks UNION, INTERSECT, EXCEPT, and SELECT DISTINCT!

Cloudflare Changelog ★★★ View Markdown
#cloudflare#infrastructure#serverless

Hey everyone, it's Shiichan! Today I've got news that R2 SQL just got even smarter.

Cloudflare Changelog developers.cloudflare.com

What was announced?

From Cloudflare's Changelog: R2 SQL now supports set operations (UNION / INTERSECT / EXCEPT) and SELECT DISTINCT. R2 SQL lets you query the Apache Iceberg tables in R2 Data Catalog directly, and this widens the range of queries you can write there.

The story so far

Until now, R2 SQL wasn't great at combining the results of multiple SELECT statements or stripping out duplicate rows in one go. Common analytical filters like "only the rows A and B share" or "the rows in A but not in B" were awkward to express in plain SQL.

What changes

From now on you can write set operations inside a single query.

  • UNION returns all rows from both queries, removing duplicates
  • UNION ALL returns all rows from both queries, keeping duplicates
  • INTERSECT returns only rows that appear in both queries
  • EXCEPT returns rows from the first query that do not appear in the second

And SELECT DISTINCT collapses duplicate rows out of your results.

Dive Deep

For example, to find "zones that had firewall blocks OR high-risk requests", it looks like this:

SELECT zone_id FROM my_namespace.firewall_events WHERE action = 'block'
UNION
SELECT zone_id FROM my_namespace.http_requests WHERE risk_score > 0.8

Swap in INTERSECT and you get just the overlap, like "zones that were blocked AND had heavy traffic", while EXCEPT gives you the difference between two sets in one shot.

SELECT DISTINCT works like this, removing duplicates while still letting you sort:

SELECT DISTINCT region, department FROM my_namespace.sales_data
WHERE total_amount > 1000
ORDER BY region, department
LIMIT 100

By the way, the post notes that when you only need a count of unique values on a large dataset and can tolerate some error, approx_distinct() stays the faster alternative.

For the full syntax, check the SQL reference, and for performance tips see Limitations and best practices.

Wrap-up

  • R2 SQL now supports the UNION / UNION ALL / INTERSECT / EXCEPT set operations and SELECT DISTINCT
  • Filters that combine multiple SELECT statements, and duplicate removal, are now easy to write in plain SQL
  • For rough unique counts there's also the faster approx_distinct() option

If you analyze your R2 Data Catalog Iceberg tables with R2 SQL, this is a lovely update that makes your queries much easier to write!

Related posts