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

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

## What was announced?

From Cloudflare's Changelog: [R2 SQL](https://developers.cloudflare.com/r2-sql/) now supports set operations (`UNION` / `INTERSECT` / `EXCEPT`) and `SELECT DISTINCT`. R2 SQL lets you query the [Apache Iceberg](https://iceberg.apache.org/) tables in [R2 Data Catalog](https://developers.cloudflare.com/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:

```sql
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:

```sql
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](https://developers.cloudflare.com/r2-sql/sql-reference/), and for performance tips see [Limitations and best practices](https://developers.cloudflare.com/r2-sql/reference/limitations-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!