How is query result pagination implemented in SQL?

In SQL, to divide query results into pages, constructions with LIMIT and OFFSET (in PostgreSQL, MySQL) or FETCH NEXT and OFFSET (in SQL Server, Oracle 12c+) are usually used.

The principle is that LIMIT sets the number of rows per page, and OFFSET specifies how many rows to skip (i.e., from which result to start). For example, to get the second page with 10 records:

SELECT * FROM table_name
ORDER BY id
LIMIT 10 OFFSET 10;

Here, LIMIT 10 is 10 records per page, OFFSET 10 skips the first 10 (the first page).

In Oracle before 12c, pagination used subqueries with ROWNUM or ROW_NUMBER().

This approach allows efficient data loading in parts, which is important for performance and user convenience.