{"id":462,"date":"2026-05-15T00:00:00","date_gmt":"2026-05-15T00:00:00","guid":{"rendered":"https:\/\/wordpress.securinsight.ca\/index.php\/2026\/05\/15\/r2-sql-r2-sql-now-supports-joins-subqueries-and-multi-table-queries-2\/"},"modified":"2026-05-15T00:00:00","modified_gmt":"2026-05-15T00:00:00","slug":"r2-sql-r2-sql-now-supports-joins-subqueries-and-multi-table-queries-2","status":"publish","type":"post","link":"https:\/\/wordpress.securinsight.ca\/index.php\/2026\/05\/15\/r2-sql-r2-sql-now-supports-joins-subqueries-and-multi-table-queries-2\/","title":{"rendered":"R2 SQL &#8211; R2 SQL now supports JOINs, subqueries, and multi-table queries"},"content":{"rendered":"<p><a href=\"https:\/\/developers.cloudflare.com\/r2-sql\/\">R2 SQL<\/a> is Cloudflare&#8217;s serverless, distributed SQL engine for querying <a href=\"https:\/\/iceberg.apache.org\/\" target=\"_blank\">Apache Iceberg<\/a> tables stored in <a href=\"https:\/\/developers.cloudflare.com\/r2\/data-catalog\/\">R2 Data Catalog<\/a>. R2 SQL runs directly on Cloudflare&#8217;s global network with no infrastructure to manage, so you can analyze data in R2 without exporting it to an external warehouse.<\/p>\n<p>R2 SQL now supports joining multiple Iceberg tables in a single query. You can combine tables with JOINs, filter with subqueries, and define multi-table CTEs to build complex analytical queries.<\/p>\n<h4>New capabilities<\/h4>\n<ul>\n<li><strong>JOINs<\/strong> \u2014 <code>INNER JOIN<\/code>, <code>LEFT JOIN<\/code>, <code>RIGHT JOIN<\/code>, <code>FULL OUTER JOIN<\/code>, <code>CROSS JOIN<\/code>, and implicit joins (comma-separated <code>FROM<\/code> with conditions in <code>WHERE<\/code>)<\/li>\n<li><strong>Subqueries<\/strong> \u2014 <code>IN<\/code> \/ <code>NOT IN<\/code>, <code>EXISTS<\/code> \/ <code>NOT EXISTS<\/code>, scalar subqueries in <code>SELECT<\/code> \/ <code>WHERE<\/code> \/ <code>HAVING<\/code>, and derived tables (subqueries in <code>FROM<\/code>)<\/li>\n<li><strong>Multi-table CTEs<\/strong> \u2014 <code>WITH<\/code> clauses can reference different tables and include JOINs<\/li>\n<li><strong>Self-joins<\/strong> \u2014 join a table with itself using different aliases<\/li>\n<li><strong>Multi-way joins<\/strong> \u2014 join three or more tables in a single query<\/li>\n<\/ul>\n<h4>Examples<\/h4>\n<h4>Two-table JOIN with aggregation<\/h4>\n<div>\n<figure>\n<pre data-language=\"sql\"><code class=\"language-sql\"><div><div><span>SELECT<\/span><span> z.domain, z.plan, <\/span><span>COUNT<\/span><span>(<\/span><span>*<\/span><span>) <\/span><span>AS<\/span><span> request_count<\/span><\/div><\/div><div><div><span>FROM<\/span><span> my_namespace.zones z<\/span><\/div><\/div><div><div><span>INNER JOIN<\/span><span> my_namespace.http_requests h <\/span><span>ON<\/span><span> z.zone_id <\/span><span>=<\/span><span> h.zone_id<\/span><\/div><\/div><div><div><span>WHERE<\/span><span> z.plan <\/span><span>=<\/span><span> <\/span><span>'enterprise'<\/span><\/div><\/div><div><div><span>GROUP BY<\/span><span> z.domain, z.plan<\/span><\/div><\/div><div><div><span>ORDER BY<\/span><span> request_count <\/span><span>DESC<\/span><\/div><\/div><div><div><span>LIMIT<\/span><span> <\/span><span>20<\/span><\/div><\/div><\/code><\/pre>\n<div>\n<div><\/div>\n<\/div>\n<\/figure>\n<\/div>\n<h4><code>EXISTS<\/code> subquery<\/h4>\n<div>\n<figure>\n<pre data-language=\"sql\"><code class=\"language-sql\"><div><div><span>SELECT<\/span><span> z.domain, z.plan<\/span><\/div><\/div><div><div><span>FROM<\/span><span> my_namespace.zones z<\/span><\/div><\/div><div><div><span>WHERE<\/span><span> <\/span><span>EXISTS<\/span><span> (<\/span><\/div><\/div><div><div><span>    <\/span><span>SELECT<\/span><span> <\/span><span>1<\/span><span> <\/span><span>FROM<\/span><span> my_namespace.firewall_events f<\/span><\/div><\/div><div><div><span>    <\/span><span>WHERE<\/span><span> f.zone_id <\/span><span>=<\/span><span> z.zone_id <\/span><span>AND<\/span><span> f.action <\/span><span>=<\/span><span> <\/span><span>'block'<\/span><\/div><\/div><div><div><span>)<\/span><\/div><\/div><div><div><span>ORDER BY<\/span><span> z.domain<\/span><\/div><\/div><div><div><span>LIMIT<\/span><span> <\/span><span>20<\/span><\/div><\/div><\/code><\/pre>\n<div>\n<div><\/div>\n<\/div>\n<\/figure>\n<\/div>\n<h4>Multi-table CTE with JOIN<\/h4>\n<div>\n<figure>\n<pre data-language=\"sql\"><code class=\"language-sql\"><div><div><span>WITH<\/span><span> top_zones <\/span><span>AS<\/span><span> (<\/span><\/div><\/div><div><div><span>    <\/span><span>SELECT<\/span><span> zone_id, <\/span><span>COUNT<\/span><span>(<\/span><span>*<\/span><span>) <\/span><span>AS<\/span><span> req_count<\/span><\/div><\/div><div><div><span>    <\/span><span>FROM<\/span><span> my_namespace.http_requests<\/span><\/div><\/div><div><div><span>    <\/span><span>GROUP BY<\/span><span> zone_id<\/span><\/div><\/div><div><div><span>    <\/span><span>ORDER BY<\/span><span> req_count <\/span><span>DESC<\/span><\/div><\/div><div><div><span>    <\/span><span>LIMIT<\/span><span> <\/span><span>50<\/span><\/div><\/div><div><div><span>),<\/span><\/div><\/div><div><div><span>zone_threats <\/span><span>AS<\/span><span> (<\/span><\/div><\/div><div><div><span>    <\/span><span>SELECT<\/span><span> zone_id, <\/span><span>COUNT<\/span><span>(<\/span><span>*<\/span><span>) <\/span><span>AS<\/span><span> threat_count<\/span><\/div><\/div><div><div><span>    <\/span><span>FROM<\/span><span> my_namespace.firewall_events<\/span><\/div><\/div><div><div><span>    <\/span><span>WHERE<\/span><span> risk_score <\/span><span>&gt;<\/span><span> <\/span><span>0<\/span><span>.<\/span><span>5<\/span><\/div><\/div><div><div><span>    <\/span><span>GROUP BY<\/span><span> zone_id<\/span><\/div><\/div><div><div><span>)<\/span><\/div><\/div><div><div><span>SELECT<\/span><span> tz.zone_id, tz.req_count, <\/span><span>COALESCE<\/span><span>(zt.threat_count, <\/span><span>0<\/span><span>) <\/span><span>AS<\/span><span> threat_count<\/span><\/div><\/div><div><div><span>FROM<\/span><span> top_zones tz<\/span><\/div><\/div><div><div><span>LEFT JOIN<\/span><span> zone_threats zt <\/span><span>ON<\/span><span> tz.zone_id <\/span><span>=<\/span><span> zt.zone_id<\/span><\/div><\/div><div><div><span>ORDER BY<\/span><span> tz.req_count <\/span><span>DESC<\/span><\/div><\/div><div><div><span>LIMIT<\/span><span> <\/span><span>20<\/span><\/div><\/div><\/code><\/pre>\n<div>\n<div><\/div>\n<\/div>\n<div><\/div>\n<\/figure>\n<\/div>\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 with joins, 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 is Cloudflare&#8217;s serverless, distributed SQL engine for querying Apache Iceberg tables stored in R2 Data Catalog. R2 SQL runs directly on Cloudflare&#8217;s global network with no infrastructure to manage, so you can analyze data in R2 without exporting it to an external warehouse. R2 SQL now supports joining multiple Iceberg tables in a [&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-462","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/posts\/462","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=462"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/posts\/462\/revisions"}],"wp:attachment":[{"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/media?parent=462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/categories?post=462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.securinsight.ca\/index.php\/wp-json\/wp\/v2\/tags?post=462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}