R2 SQL - R2 SQL now supports UNION, INTERSECT, EXCEPT, and SELECT DISTINCT

R2 SQL now supports set operations (UNION, INTERSECT, EXCEPT) and SELECT DISTINCT, expanding the range of analytical queries you can run directly on Apache Iceberg tables in R2 Data Catalog.

Set operations

Combine the results of multiple SELECT statements:

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

<div><div><span>-- Find zones that had either firewall blocks OR high-risk requests</span></div></div><div><div><span>SELECT</span><span> zone_id </span><span>FROM</span><span> my_namespace.firewall_events </span><span>WHERE</span><span> </span><span>action</span><span> </span><span>=</span><span> </span><span>'block'</span></div></div><div><div><span>UNION</span></div></div><div><div><span>SELECT</span><span> zone_id </span><span>FROM</span><span> my_namespace.http_requests </span><span>WHERE</span><span> risk_score </span><span>></span><span> </span><span>0</span><span>.</span><span>8</span></div></div>

<div><div><span>-- Find zones with both firewall blocks AND high traffic</span></div></div><div><div><span>SELECT</span><span> zone_id </span><span>FROM</span><span> my_namespace.firewall_events </span><span>WHERE</span><span> </span><span>action</span><span> </span><span>=</span><span> </span><span>'block'</span></div></div><div><div><span>INTERSECT</span></div></div><div><div><span>SELECT</span><span> zone_id </span><span>FROM</span><span> my_namespace.http_requests</span></div></div><div><div><span>GROUP BY</span><span> zone_id</span></div></div><div><div><span>HAVING</span><span> </span><span>COUNT</span><span>(</span><span>*</span><span>) </span><span>></span><span> </span><span>10000</span></div></div>

<div><div><span>-- Find enterprise zones that have not been compacted</span></div></div><div><div><span>SELECT</span><span> zone_id </span><span>FROM</span><span> my_namespace.zones </span><span>WHERE</span><span> plan </span><span>=</span><span> </span><span>'enterprise'</span></div></div><div><div><span>EXCEPT</span></div></div><div><div><span>SELECT</span><span> zone_id </span><span>FROM</span><span> my_namespace.compaction_history</span></div></div>

Select distinct

Eliminate duplicate rows from query results:

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

For large datasets where approximate results are acceptable, approx_distinct() remains a faster alternative for counting unique values.

For the full syntax reference, refer to the SQL reference. For performance guidance, refer to Limitations and best practices.