{"id":537,"date":"2026-06-08T00:00:00","date_gmt":"2026-06-08T00:00:00","guid":{"rendered":"https:\/\/wordpress.securinsight.ca\/index.php\/2026\/06\/08\/r2-sql-r2-sql-now-supports-union-intersect-except-and-select-distinct\/"},"modified":"2026-06-08T00:00:00","modified_gmt":"2026-06-08T00:00:00","slug":"r2-sql-r2-sql-now-supports-union-intersect-except-and-select-distinct","status":"publish","type":"post","link":"https:\/\/wordpress.securinsight.ca\/index.php\/2026\/06\/08\/r2-sql-r2-sql-now-supports-union-intersect-except-and-select-distinct\/","title":{"rendered":"R2 SQL &#8211; R2 SQL now supports UNION, INTERSECT, EXCEPT, and SELECT DISTINCT"},"content":{"rendered":"<p><a href=\"https:\/\/developers.cloudflare.com\/r2-sql\/\">R2 SQL<\/a> now supports set operations (<code>UNION<\/code>, <code>INTERSECT<\/code>, <code>EXCEPT<\/code>) and <code>SELECT DISTINCT<\/code>, expanding the range of analytical queries you can run directly on <a href=\"https:\/\/iceberg.apache.org\/\" target=\"_blank\">Apache Iceberg<\/a> tables in <a href=\"https:\/\/developers.cloudflare.com\/r2\/data-catalog\/\">R2 Data Catalog<\/a>.<\/p>\n<h4>Set operations<\/h4>\n<p>Combine the results of multiple <code>SELECT<\/code> statements:<\/p>\n<ul>\n<li><strong><code>UNION<\/code><\/strong> \u2014 returns all rows from both queries, removing duplicates<\/li>\n<li><strong><code>UNION ALL<\/code><\/strong> \u2014 returns all rows from both queries, including duplicates<\/li>\n<li><strong><code>INTERSECT<\/code><\/strong> \u2014 returns only rows that appear in both queries<\/li>\n<li><strong><code>EXCEPT<\/code><\/strong> \u2014 returns rows from the first query that do not appear in the second<\/li>\n<\/ul>\n<div>\n<figure>\n<pre data-language=\"sql\"><code class=\"language-sql\"><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>&gt;<\/span><span> <\/span><span>0<\/span><span>.<\/span><span>8<\/span><\/div><\/div><\/code><\/pre>\n<div>\n<div><\/div>\n<\/div>\n<\/figure>\n<\/div>\n<div>\n<figure>\n<pre data-language=\"sql\"><code class=\"language-sql\"><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>&gt;<\/span><span> <\/span><span>10000<\/span><\/div><\/div><\/code><\/pre>\n<div>\n<div><\/div>\n<\/div>\n<\/figure>\n<\/div>\n<div>\n<figure>\n<pre data-language=\"sql\"><code class=\"language-sql\"><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><\/code><\/pre>\n<div>\n<div><\/div>\n<\/div>\n<\/figure>\n<\/div>\n<h4>Select distinct<\/h4>\n<p>Eliminate duplicate rows from query results:<\/p>\n<div>\n<figure>\n<pre data-language=\"sql\"><code class=\"language-sql\"><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>&gt;<\/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><\/code><\/pre>\n<div>\n<div><\/div>\n<\/div>\n<\/figure>\n<\/div>\n<p>For large datasets where approximate results are acceptable, <code>approx_distinct()<\/code> remains a faster alternative for counting unique values.<\/p>\n<p>For the full syntax reference, refer to the <a href=\"https:\/\/developers.cloudflare.com\/r2-sql\/sql-reference\/\">SQL reference<\/a>. For performance guidance, refer to <a href=\"https:\/\/developers.cloudflare.com\/r2-sql\/reference\/limitations-best-practices\/\">Limitations and best practices<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"<p>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 \u2014 returns all rows from both queries, removing duplicates UNION ALL \u2014 returns all rows [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-537","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/posts\/537","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/comments?post=537"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/posts\/537\/revisions"}],"wp:attachment":[{"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/media?parent=537"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/categories?post=537"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/tags?post=537"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}