SQL OFFSET

The previous lesson capped how many rows come back. OFFSET answers a different question: how many rows to skip before returning any at all, the mechanism behind "page 2," "page 3," and so on.

Learning Objectives

• Skip a number of rows with OFFSET, in the syntax specific to the database you're using.
• Combine OFFSET with a row limit to build pagination.
• Understand why a large OFFSET gets slower as it grows, and what the alternative is.

OFFSET by Database

SELECT first_name, salary
FROM employees
ORDER BY id
LIMIT 10 OFFSET 20;

-- Skips the first 20 rows, then returns the next 10 (page 3, 10 per page)

SELECT first_name, salary
FROM employees
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle Pre-12c: No OFFSET Keyword

Older Oracle has no OFFSET keyword at all. The workaround nests three queries inside each other (a technique called a subquery, covered in full in Module 6, don't worry about fully following the syntax here yet): the innermost query sorts the data, the middle query assigns ROWNUM to that already-sorted result, and the outer query filters that ROWNUM range. All three levels matter, ROWNUM has to be assigned after sorting, not before, or the "rows" being numbered wouldn't be in the right order to begin with.

SELECT first_name, salary FROM (
  SELECT first_name, salary, ROWNUM AS rn FROM (
    SELECT first_name, salary FROM employees ORDER BY id
  )
)
WHERE rn > 20 AND rn <= 30;

Why a Large OFFSET Gets Slow

OFFSET doesn't skip rows for free. The database still has to scan through and discard every row before the offset, it just doesn't send them back to you. Page 2 with OFFSET 10 is cheap. Page 10,000 with OFFSET 100000 means scanning and discarding 100,000 rows on every single request, which gets measurably slower as the offset grows, even though the page size never changes.

Try It Yourself

Write a query returning page 2 of employees, 5 per page, ordered by id, for MySQL.

SELECT first_name
FROM employees
ORDER BY id
LIMIT 5 OFFSET 5;

-- Page 1 would be LIMIT 5 OFFSET 0 (rows 1-5)
-- Page 2 skips those 5, returning the next 5 (rows 6-10)

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Always pair OFFSET with an explicit ORDER BY, never rely on a database's incidental default ordering for pagination.
• Sort by a column with mostly-unique values (like id) as a tiebreaker, so page boundaries stay stable even when the main sort column repeats.
• For deep pagination over large tables, reach for keyset pagination (WHERE id > last_seen_id) instead of a large OFFSET once performance actually matters.
• Know your target database's exact syntax, OFFSET is not interchangeable with SQL Server/Oracle's OFFSET ... FETCH NEXT or Oracle pre-12c's ROWNUM subquery.

Interview Questions

Summary

This lesson covered OFFSET across all six databases, skipping rows before a limit applies to build pagination, Oracle's pre-12c double-nested ROWNUM workaround for databases with no OFFSET keyword, and why a large OFFSET gets slower as it grows, with keyset pagination as the standard fix once that matters.

That completes Module 1, SQL Basics: writing a query, filtering, sorting, inserting, updating, deleting, handling NULL, limiting results, and paginating through them, the core toolkit for reading and writing data in any SQL database.

What's Next?

Module 2, Operators and Filtering, is next, starting with what an operator actually is and the categories SQL groups them into, before AND, OR, NOT, LIKE, wildcards, IN, BETWEEN, and aliases.