SQL reference

Note

R2 SQL is in public beta. Supported SQL grammar may change over time.

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

Note

CTEs must reference a single table. Multi-table CTEs, JOINs within CTEs, and cross-table references are not supported.

---

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 total_amount DESC
LIMIT 50

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

---

LIMIT clause

Syntax

LIMIT number

• Type: Integer only
• Default: 500

Examples

SELECT * FROM my_namespace.sales_data LIMIT 100

---

EXPLAIN

Returns the execution plan for a query without running it.

EXPLAIN SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
GROUP BY department;

---

Expressions

Expressions can be used in SELECT, WHERE, GROUP BY, HAVING, and ORDER BY clauses.

Literals

SELECT 42 AS int_val, 3.14 AS float_val, 'hello' AS str_val, TRUE AS bool_val, NULL AS null_val
FROM my_namespace.sales_data LIMIT 1

Arithmetic operators

+, -, *, /, %

SELECT customer_id, total_amount * 1.1 AS total_with_tax, total_amount % 10 AS remainder
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

String concatenation

SELECT customer_id || ' - ' || region AS label
FROM my_namespace.sales_data
LIMIT 5

CASE expressions

Searched form:

SELECT customer_id,
    CASE
        WHEN total_amount > 1000 THEN 'high'
        WHEN total_amount > 100 THEN 'medium'
        ELSE 'low'
    END AS tier
FROM my_namespace.sales_data
LIMIT 10

Simple form:

SELECT customer_id,
    CASE region
        WHEN 'North' THEN 'N'
        WHEN 'South' THEN 'S'
        ELSE 'Other'
    END AS region_code
FROM my_namespace.sales_data
LIMIT 10

Type casting

-- CAST
SELECT CAST(total_amount AS INT) AS amount_int FROM my_namespace.sales_data LIMIT 5

-- TRY_CAST (returns NULL on failure instead of error)
SELECT TRY_CAST(customer_id AS INT) AS id_int FROM my_namespace.sales_data LIMIT 5

-- Shorthand (::)
SELECT total_amount::INT AS amount_int FROM my_namespace.sales_data LIMIT 5

EXTRACT

SELECT EXTRACT(YEAR FROM timestamp) AS yr,
       EXTRACT(MONTH FROM timestamp) AS mo,
       EXTRACT(DAY FROM timestamp) AS dy
FROM my_namespace.sales_data
LIMIT 1

---

Data type reference

Type	Description	Example Values
integer	Whole numbers	1, 42, -10, 0
float	Decimal numbers	1.5, 3.14, -2.7, 0.0
string	Text values	'hello', 'GET', '2024-01-01'
boolean	Boolean values	true, false
timestamp	RFC3339	'2025-09-24T01:00:00Z'
date	Date values	'2025-09-24'
struct	Named fields	struct_col['field_name']
array	Ordered list	array_col[1] (1-indexed)
map	Key-value pairs	map_keys(map_col)

---

Operator precedence

1. Comparison operators: =, !=, <, <=, >, >=, LIKE, BETWEEN, IS NULL, IS NOT NULL
2. AND (higher precedence)
3. OR (lower precedence)

Use parentheses to override default precedence:

SELECT * FROM my_namespace.sales_data WHERE (status = 404 OR status = 500) AND region = 'North'

---

Complete query examples

Basic query

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

Filtered query with sorting

SELECT customer_id, timestamp, status, total_amount
FROM my_namespace.sales_data
WHERE status >= 400 AND total_amount > 5000
ORDER BY total_amount DESC
LIMIT 50

Aggregation with HAVING

SELECT region, COUNT(*) AS region_count, AVG(total_amount) AS avg_amount
FROM my_namespace.sales_data
WHERE status = 'completed'
GROUP BY region
HAVING COUNT(*) > 1000
ORDER BY avg_amount DESC
LIMIT 20

Conditional categorization

SELECT customer_id,
    CASE
        WHEN total_amount >= 1000 THEN 'Premium'
        WHEN total_amount >= 100 THEN 'Standard'
        ELSE 'Basic'
    END AS tier,
    total_amount
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
ORDER BY total_amount DESC
LIMIT 20