Pagination -- limits result count and skips rows.
LIMIT <count>
OFFSET <skip>## Overview LIMIT and OFFSET control result set pagination. LIMIT restricts the maximum number of rows returned, and OFFSET skips a specified number of rows before returning results. Together, they enable page-based navigation through large result sets. DeltaForge also supports the SQL-standard `FETCH FIRST N ROWS ONLY` syntax, which is automatically rewritten to LIMIT before execution. ## Execution Order LIMIT and OFFSET are the last operations in the logical query evaluation sequence. They are applied after FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, and ORDER BY. This means LIMIT restricts the already-sorted, already-filtered result set. ## Behavior - **LIMIT N**: Returns at most N rows. If the query produces fewer than N rows, all rows are returned. LIMIT 0 returns zero rows but still validates the query and returns the result schema. - **OFFSET M**: Skips the first M rows of the result. The remaining rows are then subject to the LIMIT constraint if one is present. OFFSET 0 is the default and skips nothing. - **Non-deterministic results without ORDER BY**: Using LIMIT or OFFSET without ORDER BY produces non-deterministic results because the engine does not guarantee any particular row order. Always combine LIMIT/OFFSET with ORDER BY when consistent pagination is needed. - **FETCH FIRST rewrite**: DeltaForge transparently rewrites `FETCH FIRST N ROWS ONLY` to `LIMIT N` and `OFFSET M ROWS FETCH FIRST N ROWS ONLY` to `LIMIT N OFFSET M`. This rewrite happens before query planning. - **Top-N optimization**: When LIMIT is combined with ORDER BY, the engine uses a bounded heap sort that maintains only the top K rows in memory. This avoids sorting the entire result set and reduces memory consumption from O(N) to O(K). - **Large OFFSET performance**: OFFSET M requires the engine to compute and discard M rows before returning results. For large M values, this can be slow. For deep pagination on keyed data, consider keyset pagination (WHERE id > last_seen_id ORDER BY id LIMIT N) as a more efficient alternative. - **Interaction with set operations**: LIMIT and OFFSET at the end of a UNION/INTERSECT/EXCEPT apply to the combined result. To limit individual branches, place LIMIT inside each subquery. - **Subquery pushdown**: The optimizer does not push LIMIT into subqueries or below joins, because doing so would change query semantics. LIMIT is always applied as the final step. ## Compatibility LIMIT and OFFSET follow the widely adopted syntax used by PostgreSQL and MySQL. The FETCH FIRST N ROWS ONLY syntax conforms to the SQL:2008 standard. Both syntaxes are fully supported and produce identical execution plans.
-- Return at most 20 rows starting after row 40 (page 3 of 20-row pages)
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;-- Top 10 results
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY total DESC
LIMIT 10;-- OFFSET without LIMIT (skip first 100 rows, return all remaining)
SELECT * FROM events
ORDER BY event_time
OFFSET 100;-- LIMIT with zero returns no rows (useful for schema inspection)
SELECT * FROM wide_table
LIMIT 0;-- SQL-standard FETCH FIRST syntax (rewritten to LIMIT internally)
SELECT * FROM orders
ORDER BY order_date DESC
FETCH FIRST 25 ROWS ONLY;-- OFFSET with FETCH FIRST (SQL-standard pagination)
SELECT * FROM products
ORDER BY product_id
OFFSET 50 ROWS
FETCH FIRST 25 ROWS ONLY;