LIMIT, TOP, and FETCH FIRST: Pagination Syntax Compared

1. Why pagination syntax varies so much between databases

Almost every application with list views needs pagination, meaning limiting a query to a certain number of rows starting at a certain position. This need is as old as relational databases themselves, but when the major vendors developed their systems, there was no SQL standard governing this task yet. Each database therefore invented its own syntax, long before the ANSI standard delivered the OFFSET ... FETCH clause as a unified solution with SQL:2008.

The result to this day is a fragmented landscape: MySQL and PostgreSQL use the compact LIMIT ... OFFSET syntax, SQL Server historically used TOP and since SQL Server 2012 additionally the more standard-aligned OFFSET ... FETCH NEXT, and Oracle before version 12c relied exclusively on the semantically tricky ROWNUM pseudo column. Anyone writing applications for multiple databases or planning a migration needs to know these four approaches and their subtle differences in evaluation order, since pagination bugs caused by wrong assumptions about execution order are among the most common migration mistakes.

2. MySQL and PostgreSQL: LIMIT and OFFSET

MySQL and PostgreSQL share the same, very compact syntax LIMIT n OFFSET m, which limits the result set to n rows after sorting via ORDER BY while skipping the first m rows. This similarity is no coincidence, PostgreSQL deliberately kept the syntax compatible with MySQL, even though both systems are technically independent of each other. The order of clauses matters: LIMIT and OFFSET always come after ORDER BY, and a query without explicit sorting does not deliver a guaranteed stable order for pagination.

PostgreSQL additionally allows the alternative, more ANSI-aligned notation OFFSET m ROWS FETCH NEXT n ROWS ONLY, which is functionally identical to LIMIT ... OFFSET but fits better with migrations to or from SQL Server and Oracle. In practice, the shorter LIMIT notation still dominates on both databases, since it is assumed as the default in nearly every codebase, tutorial, and ORM.

-- MySQL and PostgreSQL: identical LIMIT / OFFSET syntax
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;
-- Returns rows 41 to 60, ordered by creation date descending

-- PostgreSQL also accepts the ANSI-closer alternative
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

3. SQL Server: TOP and OFFSET FETCH NEXT

SQL Server historically only offered TOP n, a clause directly after SELECT that returns only the first n rows of the sorted result set but does not allow natively skipping rows. For real pagination, developers previously had to work around this with a ROW_NUMBER() window function in a subquery, combined with a WHERE filter on the computed row number, a noticeably more cumbersome pattern than in MySQL or PostgreSQL.

Since SQL Server 2012, the much simpler, ANSI-compliant alternative OFFSET m ROWS FETCH NEXT n ROWS ONLY exists, which by now is the recommended standard way to paginate. One important restriction remains: OFFSET ... FETCH in SQL Server strictly requires an ORDER BY clause, while TOP theoretically also works without sorting, but then returns an undefined, non-repeatable result.

-- SQL Server: legacy TOP, only limits, cannot skip rows natively
SELECT TOP 20 id, title, created_at
FROM articles
ORDER BY created_at DESC;

-- SQL Server 2012+: modern OFFSET FETCH, requires ORDER BY
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- Older pagination workaround before SQL Server 2012
SELECT id, title, created_at FROM (
  SELECT id, title, created_at,
         ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
  FROM articles
) AS numbered
WHERE rn BETWEEN 41 AND 60;

4. Oracle: ROWNUM, ROW_NUMBER, and FETCH FIRST

Oracle had no dedicated pagination syntax before version 12c, only the pseudo column ROWNUM, which assigns a sequential number to each row in the result. The crucial pitfall: ROWNUM is assigned during evaluation, before ORDER BY takes effect, which is why a direct condition like WHERE ROWNUM BETWEEN 41 AND 60 in a sorted query produces wrong results. The correct, classic approach wraps the sorted query in a subquery and only applies ROWNUM to the already sorted intermediate result.

Since Oracle 12c there is finally an ANSI-compliant, much less error-prone syntax with OFFSET ... FETCH FIRST n ROWS ONLY, which delivers the same result without the ROWNUM trap. For new Oracle projects from version 12c onward, this syntax is unreservedly recommended, while legacy code using ROWNUM must be checked especially carefully for correct subquery nesting during any migration or refactoring.

-- Oracle before 12c: ROWNUM trap — this gives WRONG results
-- ROWNUM is assigned before ORDER BY, ordering happens on unnumbered rows
SELECT id, title, created_at
FROM articles
WHERE ROWNUM BETWEEN 41 AND 60
ORDER BY created_at DESC;  -- incorrect: ROWNUM numbers unsorted rows first

-- Correct classic pattern: apply ROWNUM to an already sorted subquery
SELECT id, title, created_at FROM (
  SELECT id, title, created_at, ROWNUM AS rn FROM (
    SELECT id, title, created_at FROM articles ORDER BY created_at DESC
  )
) WHERE rn BETWEEN 41 AND 60;

-- Oracle 12c+: modern, ANSI-compliant FETCH FIRST syntax
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY;

5. The ANSI SQL standard as a common denominator

With SQL:2008, the ANSI standard introduced the clause OFFSET m ROWS FETCH FIRST n ROWS ONLY, or equivalently FETCH NEXT n ROWS ONLY, as the official, vendor-neutral solution for pagination. PostgreSQL, SQL Server from version 2012, and Oracle from version 12c now all support this syntax, albeit with minor differences in optional additions like WITH TIES, which includes extra rows sharing the same sort value as the last returned row.

MySQL is the only one of the four databases discussed that still does not support this standard syntax to this day and instead sticks exclusively with LIMIT ... OFFSET. Anyone who really wants to write portable SQL code for all four systems therefore cannot avoid conditional code generation that produces either LIMIT ... OFFSET or OFFSET ... FETCH FIRST depending on the target database.

6. Row-number-based pagination as a portable fallback strategy

An alternative that works identically on all four databases is manual numbering via the window function ROW_NUMBER() OVER (ORDER BY ...), which has been part of the standard since SQL:2003 and is supported equally by MySQL from version 8.0, PostgreSQL, SQL Server, and Oracle. This method wraps the sorted query in a subquery, computes the row number, and then filters with a simple WHERE condition on the desired range.

The advantage of this row-number strategy is full portability without conditional code generation, the downside is somewhat more code to write compared to the native shorthand syntax and, in some query plans, slightly worse optimizability, since the optimizer cannot always tie the window function to the underlying index as efficiently as a native LIMIT or FETCH FIRST clause.

-- Portable pattern: works identically on MySQL 8+, PostgreSQL, SQL Server, Oracle
SELECT id, title, created_at FROM (
  SELECT id, title, created_at,
         ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
  FROM articles
) AS numbered
WHERE rn BETWEEN 41 AND 60
ORDER BY created_at DESC;
-- No LIMIT, TOP, ROWNUM, or FETCH FIRST needed — same syntax everywhere

7. Why large OFFSET values become expensive in every database

Regardless of the chosen syntax, all four databases share the same structural problem with large offset values: the database must internally actually traverse the first m + n rows of the sorted result set before it can discard the skipped m rows and return the remaining n rows. On page 1000 of a result list with 20 rows per page, this means internally reading almost 20,000 rows, regardless of whether the syntax is LIMIT, TOP with a subquery, ROWNUM, or FETCH FIRST.

This behavior is syntax-independent and rooted in the relational execution logic itself, which is why keyset pagination with a WHERE condition on the last seen sort value is the noticeably faster alternative for deep pages. This article deliberately focuses on the syntax differences of classic offset pagination between databases, while the performance strategy and comparison with keyset pagination are covered in depth in a dedicated article on this blog.

8. ORM and query builder abstractions for portable pagination

In practice, few teams write pagination syntax by hand for multiple databases and instead rely on their ORM's or query builder's abstraction. Doctrine, Eloquent, Sequelize, and SQLAlchemy all offer a unified limit()/offset() API that internally generates the appropriate native syntax depending on the configured database driver, without application code needing to know LIMIT, TOP, or ROWNUM directly.

Still, it is important to check the actually generated SQL code for critical queries, especially with older Oracle target systems, where some ORM versions historically generated the error-prone ROWNUM subquery nesting instead of the more modern FETCH FIRST syntax. An automated test that runs a sample query with an offset for each supported database and checks the result against an expected row set reliably catches such ORM regressions.

9. Pagination syntax compared directly

The following table places the four native syntax forms alongside the ANSI standard approach.

Anyone writing new code today targeting at least PostgreSQL, SQL Server 2012+, or Oracle 12c+ should prefer the ANSI syntax OFFSET ... FETCH FIRST. Only if MySQL is part of the supported systems does either LIMIT ... OFFSET as a database-specific branch or the fully portable row-number strategy from section six remain the right choice.

10. Summary

Pagination syntax differs significantly between MySQL, PostgreSQL, SQL Server, and Oracle: MySQL and PostgreSQL share the compact LIMIT ... OFFSET syntax, SQL Server historically offers TOP and, more modernly, OFFSET ... FETCH NEXT, and Oracle switched from the error-prone ROWNUM pseudo column to the ANSI-compliant FETCH FIRST clause starting with version 12c. The ANSI standard OFFSET ... FETCH FIRST is supported by three of the four systems, only MySQL is left out.

For production, portable code, either conditional syntax generation depending on the target database or the fully portable row-number strategy with ROW_NUMBER() OVER (ORDER BY ...) is worthwhile. Regardless of the chosen pagination syntax, the structural performance problem of large offset values remains and should be solved with keyset pagination for deep pages.

11. FAQ: Pagination Syntax Compared Across Databases