Joining R2 Data Catalog tables in R2 SQL
R2 SQL queries Apache Iceberg tables stored in R2 Data Catalog, so joins happen where the data already lives rather than in an external warehouse. That keeps the query path shorter, which is the main appeal when the shape of the question is analytical rather than transactional.
Supported joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN and implicit joins. You can also use self-joins, multi-way joins, derived tables, IN and EXISTS subqueries, scalar subqueries, and multi-table CTEs. In practice, that covers most of the awkward reporting work people normally push into a separate SQL engine.
The obvious use case is combining related Iceberg tables under the same namespace, such as my_namespace.zones, my_namespace.http_requests, and my_namespace.firewall_events. That lets you stitch behaviour together in one query instead of exporting data and rebuilding the same join logic elsewhere.
When R2 SQL joins make sense over single-table reads
Cloudflare R2 SQL joins make sense when the result depends on table relationships, not just filtering one table harder. If the query asks which requests came from which zones, or which firewall events lined up with a request pattern, a single-table scan starts to look clumsy very quickly.
Joins are a poor fit when the same result can be pulled from one table with a clean predicate and a few derived columns. Once the query needs repeated lookups, correlated filters, or a second table for context, the join usually becomes the simpler form even if it is not the cheapest one.
Performance is the bit that needs discipline. Cloudflare provides separate documentation for limitations and best practices, which is the quiet reminder that supported syntax and fast syntax are not the same thing. A query that reads neatly can still produce a messy join order or a wider scan than expected.
Join paths on Apache Iceberg tables in R2 Data Catalog
Iceberg tables in R2 Data Catalog make the join path predictable at the SQL layer, but the usual relational rules still apply. Join keys need to line up cleanly, and the query shape should match the data shape rather than fight it.
Self-joins are useful when the same table needs to be compared with itself under different aliases. That comes up in duplicate detection, sequence checks, and other small bits of SQL archaeology that never feel glamorous and always take longer than expected.
Use COALESCE where a nullable join key would otherwise leave gaps in the output. It does not fix bad data, but it does stop the query from pretending missing values are more informative than they are.
Query shapes that stay readable once the tables multiply
Multi-table queries tend to become unreadable when every filter sits inline and every join condition repeats the same logic. That is where the structure starts to matter more than the syntax.
Derived tables and CTEs keep the join surface smaller. A CTE can hold a repeated filter once, then feed that result into several joins without copying the same predicate around the query. That is cleaner, and it also makes the cost of each stage easier to spot when something slows down.
Subqueries are still useful, but only when they pull their weight. IN and EXISTS are better than a join when the query only needs to test membership or presence. Scalar subqueries fit when a single value belongs in the SELECT, WHERE or HAVING clause and does not justify another joined table.
sql
WITH recentrequests AS (
SELECT *
FROM mynamespace.httprequests
WHERE ts >= CURRENTDATE – INTERVAL ‘7 days’
),
flaggedevents AS (
SELECT *
FROM mynamespace.firewallevents
WHERE riskscore > 0.5
)
SELECT
z.zoneid,
z.zonename,
COUNT(*) AS requestcount
FROM mynamespace.zones z
JOIN recentrequests r
ON r.zoneid = z.zoneid
LEFT JOIN flaggedevents f
ON f.zoneid = z.zoneid
GROUP BY z.zoneid, z.zonename
LIMIT 20;
That shape keeps repeated filters in one place and leaves the joins visible at the top level. It is easier to read, easier to trim, and less likely to turn into a pile of nested parentheses after the third edit.
CROSS JOIN has a place, but usually not the one people think it has. Use it when the full combination set is deliberate, not when a join condition has simply gone missing. That sort of mistake can produce a very convincing bill and a very unconvincing result set.
