limitoffset

LIMIT and OFFSET are SQL clauses used to constrain the number of rows returned by a query and to skip a number of initial rows, enabling pagination. The term LIMIT OFFSET is commonly used to describe their combined effect: LIMIT specifies how many rows to return, while OFFSET specifies how many rows to skip.

Dialect variations are common. In PostgreSQL and SQLite, you typically write: SELECT ... FROM ... ORDER BY column LIMIT 10 OFFSET 20. MySQL also supports this form as LIMIT 20 OFFSET 10 or the alternative LIMIT 10, 20 (which means 20 rows starting after the first 10). SQL Server uses a different approach with OFFSET-FETCH in conjunction with ORDER BY: ORDER BY column ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY. Oracle uses a similar pattern: ORDER BY column ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY. In all cases, a stable ORDER BY clause is important to ensure consistent paging results.

Examples: In PostgreSQL: SELECT * FROM products ORDER BY id ASC LIMIT 10 OFFSET 20. In MySQL: SELECT * FROM products ORDER BY id ASC LIMIT 20, 10 or LIMIT 10 OFFSET 20. In SQL Server: SELECT * FROM products ORDER BY id ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY.

Considerations: Without an ORDER BY, the row order may be non-deterministic, making pagination unreliable. Large offsets can be slow on large datasets; alternatives include keyset pagination (cursor-based) that uses a last-seen value for navigation and can offer better performance for deep paging. LIMIT and OFFSET remain a simple and widely supported method for basic pagination.