SQL reference

R2 SQL is Cloudflare's serverless, distributed, analytics query engine for querying Apache Iceberg ↗ tables stored in R2 Data Catalog. This page documents the supported SQL syntax.

Query syntax

SELECT column_list | expression | aggregation_function
FROM namespace_name.table_name
[WHERE conditions]
[GROUP BY column_list]
[HAVING conditions]
[ORDER BY expression [ASC | DESC]]
[LIMIT number]

Schema discovery commands

SHOW DATABASES

Lists all available namespaces.

SHOW DATABASES;

SHOW NAMESPACES

Alias for SHOW DATABASES. Lists all available namespaces.

SHOW NAMESPACES;

SHOW TABLES

Lists all tables within a specific namespace.

SHOW TABLES IN namespace_name;

DESCRIBE

Describes the structure of a table, showing column names and data types.

DESCRIBE namespace_name.table_name;

SELECT clause

Syntax

SELECT column_specification [, column_specification, ...]

Column specification

Column name: column_name
All columns: *
Qualified wildcard: table_name.*
Column alias: column_name AS alias
Expressions: arithmetic, function calls, CASE expressions, and casts

Examples

SELECT * FROM my_namespace.sales_data LIMIT 10
SELECT customer_id, region, total_amount FROM my_namespace.sales_data LIMIT 10
SELECT region, total_amount * 1.1 AS total_with_tax FROM my_namespace.sales_data LIMIT 10

Common table expressions (CTEs)

CTEs let you define named temporary result sets using WITH that you can reference in the main query. All CTEs must reference the same single table.

Syntax

WITH cte_name AS (
    SELECT ...
    FROM namespace_name.table_name
    [WHERE ...]
)
SELECT ... FROM cte_name

Chained CTEs

A CTE can reference a previously defined CTE. All CTEs in the chain must derive from the same underlying table.

WITH filtered AS (
    SELECT customer_id, department, total_amount
    FROM my_namespace.sales_data
    WHERE total_amount > 0
),
summary AS (
    SELECT department,
           COUNT(*) AS order_count,
           round(AVG(total_amount), 2) AS avg_amount
    FROM filtered
    GROUP BY department
)
SELECT *
FROM summary
WHERE order_count > 100
ORDER BY avg_amount DESC

FROM clause

Syntax

SELECT * FROM namespace_name.table_name

R2 SQL queries reference exactly one table, specified as namespace_name.table_name.

WHERE clause

Syntax

SELECT * FROM namespace_name.table_name WHERE condition [AND | OR condition ...]

Conditions

Comparison operators

=, !=, <>, <, >, <=, >=

Null checks

column_name IS NULL
column_name IS NOT NULL

Boolean checks

IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE
IS UNKNOWN, IS NOT UNKNOWN

Range

column_name BETWEEN value1 AND value2
column_name NOT BETWEEN value1 AND value2

List membership

column_name IN ('value1', 'value2')
column_name NOT IN ('value1', 'value2')

Pattern matching

column_name LIKE 'pattern'
column_name NOT LIKE 'pattern'
column_name ILIKE 'pattern' (case-insensitive)
column_name NOT ILIKE 'pattern'
column_name SIMILAR TO 'regex_pattern'

Logical operators

AND
OR
NOT

Examples

SELECT * FROM my_namespace.sales_data
WHERE timestamp BETWEEN '2025-09-24T01:00:00Z' AND '2025-09-25T01:00:00Z'

SELECT * FROM my_namespace.sales_data
WHERE status = 200 AND response_time > 1000

SELECT * FROM my_namespace.sales_data
WHERE (region = 'North' OR region = 'South')
  AND total_amount IS NOT NULL

SELECT * FROM my_namespace.sales_data
WHERE department ILIKE '%eng%'

GROUP BY clause

Syntax

SELECT column_list, aggregation_function(column)
FROM namespace_name.table_name
[WHERE conditions]
GROUP BY column_list

Examples

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department

SELECT department, category, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY department, category

HAVING clause

Syntax

SELECT column_list, aggregation_function(column) AS alias
FROM namespace_name.table_name
GROUP BY column_list
HAVING aggregation_function(column) comparison_operator value

Examples

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department
HAVING COUNT(*) > 1000

SELECT region, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY region
HAVING SUM(total_amount) > 1000000

ORDER BY clause

Syntax

ORDER BY expression [ASC | DESC] [, expression [ASC | DESC], ...]

ASC: Ascending order (default)
DESC: Descending order
Multi-column ordering is supported

Examples

SELECT customer_id, total_amount
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
ORDER BY