How Do LIMIT and OFFSET Work in SQL?
Understand LIMIT and OFFSET in SQL: how they slice result sets for pagination, why ORDER BY matters, dialect differences, and worked examples.
Expected Interview Answer
LIMIT restricts a query to return at most a given number of rows, and OFFSET skips a given number of rows before returning any — together they let you fetch a specific slice of a result set, which is the basis of pagination.
LIMIT n returns the first n rows of the result; OFFSET m skips the first m rows first, so LIMIT 10 OFFSET 20 returns rows 21 to 30. Because a table has no inherent order, LIMIT and OFFSET are only meaningful with an ORDER BY clause that makes the ordering deterministic. Syntax varies by dialect: MySQL and PostgreSQL use LIMIT/OFFSET, while SQL Server and Oracle use OFFSET ... FETCH NEXT ... ROWS ONLY.
- Enables page-by-page browsing of large result sets
- Reduces data transferred and memory used per query
- Simple to combine with ORDER BY for stable ordering
- Lets you fetch a top-N result quickly
- Supported across most major SQL databases
AI Mentor Explanation
LIMIT and OFFSET are like asking for a specific stretch of an innings highlights reel: OFFSET says skip the first 20 balls, LIMIT says now show me the next 10. Without a batting order to sort by, saying 'the next ten' is meaningless, so you always fix the sequence first, then take your slice.
Step-by-Step Explanation
Step 1
Write the base query
Start with the SELECT and WHERE that produce the full result set you want to slice.
Step 2
Add a deterministic ORDER BY
Sort by a stable column (or set of columns) so the row sequence is well-defined.
Step 3
Apply LIMIT
Add LIMIT n to cap how many rows are returned in one page.
Step 4
Apply OFFSET for pages
Add OFFSET m to skip earlier pages; page p uses OFFSET (p-1)*n.
Step 5
Mind dialect differences
Use OFFSET ... FETCH NEXT ... ROWS ONLY on SQL Server/Oracle instead of LIMIT/OFFSET.
What Interviewer Expects
- Clear definition of both LIMIT and OFFSET
- Knowing LIMIT/OFFSET need ORDER BY to be deterministic
- The pagination formula OFFSET = (page-1) * pageSize
- Awareness of dialect syntax differences
- Understanding OFFSET can be slow on large offsets
Common Mistakes
- Using LIMIT/OFFSET without ORDER BY and expecting stable order
- Swapping the meaning of LIMIT and OFFSET
- Off-by-one errors in the OFFSET page formula
- Assuming LIMIT/OFFSET syntax is identical in every database
- Using huge OFFSET values and ignoring the performance cost
Best Answer (HR Friendly)
“LIMIT sets the maximum number of rows a query returns, and OFFSET tells the database how many rows to skip first. Together they fetch one page of results at a time, which is how websites show data ten or twenty rows per page.”
Code Example
SELECT id, name, created_at
FROM users
ORDER BY created_at DESC, id
LIMIT 10 OFFSET 20;SELECT id, name, created_at
FROM users
ORDER BY created_at DESC, id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;Follow-up Questions
- Why do LIMIT and OFFSET require an ORDER BY?
- How do you compute OFFSET for page number p with page size n?
- Why is large OFFSET pagination slow, and what is keyset pagination?
- How does LIMIT/OFFSET differ across MySQL, PostgreSQL and SQL Server?
- What does LIMIT 0 return and when is it useful?
MCQ Practice
1. What does LIMIT 10 OFFSET 20 return?
OFFSET 20 skips the first 20 rows, then LIMIT 10 returns the next 10, i.e. rows 21 to 30.
2. Why should LIMIT/OFFSET be paired with ORDER BY?
Tables have no inherent order, so without ORDER BY the same page query can return different rows each time.
3. For page size 10, which OFFSET fetches page 4?
OFFSET = (page-1) * pageSize = (4-1) * 10 = 30.
Flash Cards
What does LIMIT do? — Caps the number of rows a query returns to at most n.
What does OFFSET do? — Skips the first m rows of the result before returning any.
OFFSET formula for page p? — OFFSET = (p - 1) * pageSize.
SQL Server / Oracle equivalent? — OFFSET m ROWS FETCH NEXT n ROWS ONLY, with an ORDER BY.