OFFSET

Think of OFFSET like telling a waiter, “skip the first few plates and bring me the next ones.” Interviewers love it because it looks simple, but it hides a big paging and performance trap.

Question: What does OFFSET do in SQL?

Answer: OFFSET skips a number of rows in the result set and then returns the rows after that. It is usually paired with ORDER BY and often with LIMIT or FETCH to build pages of results. The important idea is that it does not delete rows from the table; it only changes which rows appear in the query result.

Interview-Ready Answer: “OFFSET skips the first N rows of an ordered result set, so it’s commonly used for pagination. I always pair it with ORDER BY because without a stable sort, the skipped rows can change between runs. One practical detail is that large offsets can get slow because the database may still have to read and discard many rows.”

What it is

OFFSET is a clause that tells SQL how many rows to ignore before starting to return rows. You can think of it as slicing a list after it has been sorted. The most important technical word here is deterministic, which means “always producing the same result for the same inputs.” To make paging deterministic, you need a stable ORDER BY.

How it works under the hood

1. The database builds the candidate rows from FROM, WHERE, joins, and any grouping.
2. If you use ORDER BY, it sorts those rows into a defined order.
3. OFFSET N tells the engine to skip the first N rows in that ordered stream.
4. If you also use LIMIT or FETCH NEXT, the engine returns only the next chunk after the skipped rows.
5. If the offset is larger than the available rows, the result is empty. That is not an error; it just means there is nothing left to show.

Why interviewers care

OFFSET is a classic pagination tool, but it has two common traps: unstable ordering and poor performance on deep pages. For example, page 1 may use OFFSET 0, page 2 uses OFFSET 20, and page 500 uses OFFSET 9980. The deeper you go, the more rows the database may need to walk past just to reach your page.

OFFSET vs keyset pagination

When a page number is small, OFFSET is simple and readable. When users jump far into a large dataset, a different approach called keyset pagination (also called “seek pagination”) is usually faster. Keyset pagination uses the last seen sort key, such as WHERE id > 5000, instead of counting and skipping many rows.

Performance notes

Cost depends on the plan. If the database can use an index that matches the ORDER BY, it may walk the index and discard rows until it reaches the offset. That is still roughly proportional to the offset, so OFFSET 100000 can be much slower than OFFSET 20. If the query must sort first, the sort itself can dominate, often behaving like O(n log n) for the sort plus extra work to skip rows.

In practical terms, a page size of 20 or 50 is common in apps. A deep offset like 100,000 can mean scanning or walking past a huge number of rows just to show a tiny page. That is why many teams switch to keyset pagination for feeds, logs, timelines, and large admin grids.

Important edge cases

• Without ORDER BY: the database may return rows in any physical order, so page 2 can overlap page 1 or miss rows.
• Negative offsets: most databases reject them with an error.
• Concurrent inserts/updates: rows can move between pages if the sort order changes between requests.
• Empty page: a too-large offset simply returns no rows.

Memory hook: “Sort first, then skip.” If you remember that one line, you’ll avoid most OFFSET mistakes.

Real-world story

Imagine a checkout service that has an admin screen listing recent orders. Support agents page through orders 25 at a time using OFFSET and LIMIT, sorted by created_at DESC. This works well when the list is short and the order is stable.

What goes wrong: an engineer forgets the ORDER BY, or sorts only by a non-unique column like created_at when many orders share the same timestamp. At peak traffic, new orders arrive while agents are paging. Suddenly page 2 shows duplicates from page 1, some orders seem to vanish, and support thinks the database is “losing” records. In logs, nothing is obviously broken; the bug is logical, not a crash. The fix is to use a stable ordering such as created_at DESC, id DESC, and for large datasets, consider keyset pagination instead of deep offsets.

Follow-up & Tricky Questions:

• Why should OFFSET always be paired with ORDER BY?
  Because without a stable sort, the database can return rows in a different physical order each time. That makes pages overlap, skip rows, or reshuffle between requests.
• Is OFFSET zero-based or one-based?
  It is effectively zero-based for paging: OFFSET 0 means “skip nothing.” Page 1 usually maps to offset 0, not 1.
• Why can large offsets be slow?
  The engine may still need to read, sort, or walk past all skipped rows before it can return the page. Skipping 100,000 rows is much more work than skipping 20 rows.
• What is the difference between OFFSET and LIMIT?
  OFFSET decides where the page starts; LIMIT decides how many rows to return. They usually work together to express “skip some, then take some.”
• How do I page safely when data changes between requests?
  Use a deterministic sort with a unique tiebreaker, or switch to keyset pagination if rows are inserted or updated frequently.

Tricky / Gotcha Questions:

• Does OFFSET delete or hide rows in the table?
  No. It only changes the result set for that query. The underlying data stays unchanged.
• Can I use OFFSET without ORDER BY?
  Many databases allow it, but the result is not reliable for paging. It is technically legal in some systems, but it is usually a bad idea in production.
• If I ask for a page beyond the end, do I get an error?
  Usually no. You get an empty result set because there are no rows after that skip point.

Common Mistakes:

• Using OFFSET without ORDER BY. Correction: always sort first, and include a unique tiebreaker like id.
• Thinking OFFSET filters rows. Correction: it does not filter by value; it skips a number of rows after sorting.
• Ignoring deep-page performance. Correction: large offsets can be slow; use keyset pagination for large feeds or endless scrolling.
• Forgetting that page data can shift between requests. Correction: if rows are inserted or updated frequently, use a stable order or a cursor-like approach.

Memory Hook: “Sort, then skip.” Imagine a librarian lining up books alphabetically, then telling you to ignore the first 100 before handing you the next 20.

Cheat Sheet:

• OFFSET skips rows in the result set.
• It is usually used with ORDER BY and LIMIT/FETCH.
• OFFSET 0 means no rows skipped.
• Large offsets can be slow because the database may still process skipped rows.
• Use a unique tiebreaker in sorting to keep pagination stable.

Practice Tasks:

• Write a query that returns page 3 of 10 rows each from a table ordered by created_at and id.
• Change the example so it returns the newest orders first, then verify page 2 still looks stable.
• Rewrite the paging query as keyset pagination using the last seen id instead of OFFSET.