1# R2 SQL API Reference
2 
3SQL syntax, functions, operators, and data types for R2 SQL queries.
4 
5## SQL Syntax
6 
7```sql
8SELECT column_list | aggregation_function
9FROM [namespace.]table_name
10WHERE conditions
11[GROUP BY column_list]
12[HAVING conditions]
13[ORDER BY column | aggregation_function [DESC | ASC]]
14[LIMIT number]
15```
16 
17## Schema Discovery
18 
19```sql
20SHOW DATABASES;           -- List namespaces
21SHOW NAMESPACES;          -- Alias for SHOW DATABASES
22SHOW SCHEMAS;             -- Alias for SHOW DATABASES
23SHOW TABLES IN namespace; -- List tables in namespace
24DESCRIBE namespace.table; -- Show table schema, partition keys
25```
26 
27## SELECT Clause
28 
29```sql
30-- All columns
31SELECT * FROM logs.http_requests;
32 
33-- Specific columns
34SELECT user_id, timestamp, status FROM logs.http_requests;
35```
36 
37**Limitations:** No column aliases, expressions, or nested column access
38 
39## WHERE Clause
40 
41### Operators
42 
43| Operator | Example |
44|----------|---------|
45| `=`, `!=`, `<`, `<=`, `>`, `>=` | `status = 200` |
46| `LIKE` | `user_agent LIKE '%Chrome%'` |
47| `BETWEEN` | `timestamp BETWEEN '2025-01-01T00:00:00Z' AND '2025-01-31T23:59:59Z'` |
48| `IS NULL`, `IS NOT NULL` | `email IS NOT NULL` |
49| `AND`, `OR` | `status = 200 AND method = 'GET'` |
50 
51Use parentheses for precedence: `(status = 404 OR status = 500) AND method = 'POST'`
52 
53## Aggregation Functions
54 
55| Function | Description |
56|----------|-------------|
57| `COUNT(*)` | Count all rows |
58| `COUNT(column)` | Count non-null values |
59| `COUNT(DISTINCT column)` | Count unique values |
60| `SUM(column)`, `AVG(column)` | Numeric aggregations |
61| `MIN(column)`, `MAX(column)` | Min/max values |
62 
63```sql
64-- Multiple aggregations with GROUP BY
65SELECT region, COUNT(*), SUM(amount), AVG(amount)
66FROM sales.transactions
67WHERE sale_date >= '2024-01-01'
68GROUP BY region;
69```
70 
71## HAVING Clause
72 
73Filter aggregated results (after GROUP BY):
74 
75```sql
76SELECT category, SUM(amount)
77FROM sales.transactions
78GROUP BY category
79HAVING SUM(amount) > 10000;
80```
81 
82## ORDER BY Clause
83 
84Sort results by:
85- **Partition key columns** - Always supported
86- **Aggregation functions** - Supported via shuffle strategy
87 
88```sql
89-- Order by partition key
90SELECT * FROM logs.requests ORDER BY timestamp DESC LIMIT 100;
91 
92-- Order by aggregation (repeat function, aliases not supported)
93SELECT region, SUM(amount)
94FROM sales.transactions
95GROUP BY region
96ORDER BY SUM(amount) DESC;
97```
98 
99**Limitations:** Cannot order by non-partition columns. See [gotchas.md](gotchas.md#order-by-limitations)
100 
101## LIMIT Clause
102 
103```sql
104SELECT * FROM logs.requests LIMIT 100;
105```
106 
107| Setting | Value |
108|---------|-------|
109| Min | 1 |
110| Max | 10,000 |
111| Default | 500 |
112 
113**Always use LIMIT** to enable early termination optimization.
114 
115## Data Types
116 
117| Type | SQL Literal | Example |
118|------|-------------|---------|
119| `integer` | Unquoted number | `42`, `-10` |
120| `float` | Decimal number | `3.14`, `-0.5` |
121| `string` | Single quotes | `'hello'`, `'GET'` |
122| `boolean` | Keyword | `true`, `false` |
123| `timestamp` | RFC3339 string | `'2025-01-01T00:00:00Z'` |
124| `date` | ISO 8601 date | `'2025-01-01'` |
125 
126### Type Safety
127 
128- Quote strings with single quotes: `'value'`
129- Timestamps must be RFC3339: `'2025-01-01T00:00:00Z'` (include timezone)
130- Dates must be ISO 8601: `'2025-01-01'` (YYYY-MM-DD)
131- No implicit conversions
132 
133```sql
134-- ✅ Correct
135WHERE status = 200 AND method = 'GET' AND timestamp > '2025-01-01T00:00:00Z'
136 
137-- ❌ Wrong
138WHERE status = '200'              -- string instead of integer
139WHERE timestamp > '2025-01-01'    -- missing time/timezone
140WHERE method = GET                -- unquoted string
141```
142 
143## Query Result Format
144 
145JSON array of objects:
146 
147```json
148[
149  {"user_id": "user_123", "timestamp": "2025-01-15T10:30:00Z", "status": 200},
150  {"user_id": "user_456", "timestamp": "2025-01-15T10:31:00Z", "status": 404}
151]
152```
153 
154## See Also
155 
156- [patterns.md](patterns.md) - Query examples and use cases
157- [gotchas.md](gotchas.md) - SQL limitations and error handling
158- [configuration.md](configuration.md) - Setup and authentication
159