OFFSET for pagination in SQL - Deep Dive

Overview - OFFSET for pagination

What is it?

OFFSET is a command used in SQL to skip a specific number of rows before starting to return rows from a query. It helps to divide large sets of data into smaller parts called pages. This is useful when you want to show data in chunks, like pages in a book, instead of all at once. OFFSET is often used together with LIMIT to control how many rows to show per page.

Why it matters

Without OFFSET, displaying large amounts of data would be slow and overwhelming because all data would load at once. OFFSET allows websites and apps to load data in small, manageable pieces, improving speed and user experience. It solves the problem of handling big data sets by letting users navigate through pages smoothly.

Where it fits

Before learning OFFSET, you should understand basic SQL SELECT queries and the LIMIT clause. After OFFSET, you can learn about more advanced pagination techniques, such as keyset pagination or cursor-based pagination, which improve performance for very large data sets.

Mental Model

Core Idea

OFFSET tells the database to skip a certain number of rows before returning the next set of rows, enabling easy division of data into pages.

Think of it like...

Imagine a book where you want to start reading from page 5 instead of page 1. OFFSET is like flipping past the first 4 pages to start reading exactly where you want.

┌───────────────┐
│ Full dataset  │
│  Row 1       │
│  Row 2       │
│  Row 3       │
│  Row 4       │
│  Row 5       │
│  Row 6       │
│  ...         │
└───────────────┘
      ↓ OFFSET 4
┌───────────────┐
│ Returned rows │
│  Row 5       │
│  Row 6       │
│  ...         │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic SELECT query review

Concept: Understanding how to retrieve data from a table using SELECT.

A SELECT query asks the database to give you rows from a table. For example, SELECT * FROM employees; returns all rows and columns from the employees table.

Result

All rows and columns from the employees table are shown.

Knowing how to get data is the first step before learning how to control which parts of data to see.

2

Foundation

Using LIMIT to restrict rows

Concept: LIMIT controls how many rows the query returns.

Adding LIMIT 5 to a query returns only the first 5 rows. For example, SELECT * FROM employees LIMIT 5; shows just 5 rows.

Result

Only 5 rows from the employees table are shown.

LIMIT helps manage large data by showing only a small part, making data easier to handle.

3

Intermediate

Introducing OFFSET to skip rows

🤔Before reading on: do you think OFFSET 3 LIMIT 5 returns rows 3 to 7 or rows 4 to 8? Commit to your answer.

Concept: OFFSET skips a number of rows before starting to return rows.

Using OFFSET 3 LIMIT 5 means skip the first 3 rows, then return the next 5 rows. For example, SELECT * FROM employees OFFSET 3 LIMIT 5; skips rows 1, 2, 3 and returns rows 4 to 8.

Result

Rows 4 to 8 from the employees table are shown.

Understanding OFFSET lets you jump to any part of the data, enabling page-by-page viewing.

4

Intermediate

Combining OFFSET and LIMIT for pagination

🤔Before reading on: if page size is 10, what OFFSET value shows page 3? Commit to your answer.

Concept: OFFSET and LIMIT together create pages of data by skipping and limiting rows.

To show page 3 with 10 rows per page, OFFSET should be (3-1)*10 = 20. Query: SELECT * FROM employees LIMIT 10 OFFSET 20; This skips first 20 rows and shows next 10 rows.

Result

Rows 21 to 30 from employees are shown, representing page 3.

Knowing how to calculate OFFSET for pages helps build user-friendly data navigation.

5

Intermediate

OFFSET behavior with ORDER BY clause

Concept: OFFSET works after sorting data with ORDER BY to ensure consistent pages.

If you want pages sorted by employee name, use ORDER BY name with OFFSET and LIMIT. For example: SELECT * FROM employees ORDER BY name LIMIT 10 OFFSET 20; This sorts employees by name, then skips 20 rows, then returns 10 rows.

Result

Page 3 of employees sorted by name is shown.

Sorting before OFFSET ensures pages show consistent and expected data order.

6

Advanced

Performance issues with large OFFSET values

🤔Before reading on: do you think OFFSET 1000000 is fast or slow? Commit to your answer.

Concept: Large OFFSET values can slow down queries because the database must count and skip many rows.

When OFFSET is very large, the database still scans all skipped rows internally, which takes time. For example, SELECT * FROM employees LIMIT 10 OFFSET 1000000; can be slow on big tables.

Result

Query runs slowly or uses more resources.

Knowing OFFSET's cost helps choose better pagination methods for big data.

7

Expert

Alternatives to OFFSET for efficient pagination

🤔Before reading on: do you think cursor-based pagination is better or worse than OFFSET? Commit to your answer.

Concept: Cursor-based pagination uses a known position (like an ID) instead of OFFSET to improve speed.

Instead of OFFSET, use WHERE id > last_seen_id LIMIT 10 to get next page. This avoids scanning skipped rows and is faster for large tables.

Result

Queries run faster and scale better with large data.

Understanding alternatives to OFFSET prepares you for building high-performance applications.

Under the Hood

When a query with OFFSET runs, the database engine processes rows in order. It counts and skips the number of rows specified by OFFSET, then starts returning rows up to the LIMIT. Internally, the skipped rows are still scanned but not returned. This means OFFSET does not reduce the work done, only what is sent back.

Why designed this way?

OFFSET was designed to provide a simple way to paginate results without complex state. It fits well with SQL's set-based model and is easy to understand and use. However, it trades off performance for simplicity, especially with large offsets.

┌───────────────┐
│ Query starts  │
├───────────────┤
│ Scan rows     │
├───────────────┤
│ Skip OFFSET N │
├───────────────┤
│ Return LIMIT M│
└───────────────┘

Myth Busters - 3 Common Misconceptions

Quick: Does OFFSET 0 return the first row or skip all rows? Commit yes or no.

Common Belief: OFFSET 0 skips all rows and returns nothing.

Reality: OFFSET 0 means skip zero rows, so it returns from the first row.

Why it matters: Misunderstanding OFFSET 0 can cause missing data or empty pages in pagination.

Quick: Does OFFSET improve query speed by skipping rows internally? Commit yes or no.

Common Belief: OFFSET makes queries faster by skipping rows early.

Reality: OFFSET still scans all skipped rows internally; it only skips sending them back.

Why it matters: Assuming OFFSET is fast can lead to slow applications with large data.

Quick: Is OFFSET the best method for all pagination needs? Commit yes or no.

Common Belief: OFFSET is always the best way to paginate data.

Reality: For very large data sets, cursor-based pagination is often better for performance.

Why it matters: Using OFFSET blindly can cause scalability problems in production.

Expert Zone

1

OFFSET combined with ORDER BY is essential to ensure consistent paging results; without ORDER BY, pages may show overlapping or missing rows.

2

Some databases optimize OFFSET internally for small values but degrade quickly as OFFSET grows, so performance varies by system.

3

OFFSET can cause issues with data changes between pages, leading to missing or duplicated rows if the underlying data changes during pagination.

When NOT to use

Avoid OFFSET for very large tables or real-time data where rows change often. Instead, use cursor-based pagination with WHERE clauses on indexed columns like IDs or timestamps for better performance and consistency.

Production Patterns

In production, OFFSET with LIMIT is common for simple pagination in admin panels or small datasets. For user-facing apps with large data, cursor-based pagination or keyset pagination is preferred to maintain speed and avoid data inconsistency.

Connections

Cursor-based pagination

Alternative approach to OFFSET pagination

Knowing OFFSET's limitations helps understand why cursor-based pagination uses positions instead of counts for better performance.

Data streaming

Both handle large data in chunks

OFFSET pagination and data streaming share the idea of processing data in parts to avoid overload and improve user experience.

Memory paging in operating systems

Similar concept of skipping and loading data in pages

Understanding OFFSET is like understanding how OS loads memory pages, showing how systems manage large data efficiently.

Common Pitfalls

#1 Using OFFSET without ORDER BY causes inconsistent pages.

Wrong approach:

SELECT * FROM employees LIMIT 10 OFFSET 20;

Correct approach:

SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

Root cause:

Without ORDER BY, the database returns rows in arbitrary order, so pages may overlap or skip rows.

#2 Using very large OFFSET values on big tables causes slow queries.

Wrong approach:

SELECT * FROM employees LIMIT 10 OFFSET 1000000;

Correct approach:

SELECT * FROM employees WHERE id > last_seen_id LIMIT 10;

Root cause:

OFFSET requires scanning all skipped rows internally, which is slow for large numbers.

#3 Assuming OFFSET 0 returns no rows.

Wrong approach:

SELECT * FROM employees OFFSET 0 LIMIT 10; -- expecting empty result

Correct approach:

SELECT * FROM employees OFFSET 0 LIMIT 10; -- returns first 10 rows

Root cause:

Misunderstanding that OFFSET 0 means skip zero rows, so it returns from the start.

Key Takeaways

OFFSET lets you skip a set number of rows before returning results, enabling simple pagination.

OFFSET works best with LIMIT and ORDER BY to create consistent, manageable pages of data.

Large OFFSET values can slow down queries because the database still processes skipped rows internally.

For large or changing datasets, cursor-based pagination is a better alternative to OFFSET.

Understanding OFFSET's behavior and limits helps build efficient and user-friendly data navigation.

Practice

1. What does the OFFSET clause do in an SQL query?

easy

A. It skips a specified number of rows before returning the result set.

B. It limits the number of rows returned by the query.

C. It sorts the rows in ascending order.

D. It groups rows based on a column.

Solution

1. Step 1: Understand OFFSET purpose

   The OFFSET clause tells SQL to skip a certain number of rows before starting to return rows.

2. Step 2: Compare with other clauses

   LIMIT controls how many rows to return, ORDER BY sorts rows, GROUP BY groups rows. OFFSET specifically skips rows.

3. Final Answer:

   It skips a specified number of rows before returning the result set. -> Option A

4. Quick Check:

   OFFSET = skip rows [OK]

Hint: OFFSET skips rows; LIMIT controls count [OK]

Common Mistakes:

• Confusing OFFSET with LIMIT
• Thinking OFFSET sorts rows
• Assuming OFFSET groups rows

2. Which of the following is the correct syntax to skip the first 10 rows in a query?

easy

A. SELECT * FROM table OFFSET;

B. SELECT * FROM table SKIP 10;

C. SELECT * FROM table LIMIT 10 OFFSET;

D. SELECT * FROM table OFFSET 10;

Solution

1. Step 1: Recall OFFSET syntax

   The correct syntax is to write OFFSET followed by the number of rows to skip, e.g., OFFSET 10.

2. Step 2: Check each option

   SELECT * FROM table OFFSET 10; uses correct syntax. SELECT * FROM table SKIP 10; uses SKIP which is invalid. SELECT * FROM table LIMIT 10 OFFSET; misplaces OFFSET. SELECT * FROM table OFFSET; lacks a number.

3. Final Answer:

   SELECT * FROM table OFFSET 10; -> Option D

4. Quick Check:

   OFFSET number correct syntax [OK]

Hint: Use OFFSET number after FROM clause [OK]

Common Mistakes:

• Using SKIP instead of OFFSET
• Omitting the number after OFFSET
• Placing OFFSET before FROM

3. Given the table Employees with columns id and name, what will this query return?

SELECT id, name FROM Employees ORDER BY id ASC LIMIT 3 OFFSET 2;

medium

A. Rows with the first 3 employees ordered by id.

B. Rows 1, 2, and 3 ordered by id ascending.

C. Rows 3, 4, and 5 ordered by id ascending.

D. Rows 2, 3, and 4 ordered by id ascending.

Solution

1. Step 1: Understand OFFSET and LIMIT effect

   OFFSET 2 skips the first 2 rows. LIMIT 3 returns the next 3 rows after skipping.

2. Step 2: Apply ORDER BY

   Rows are ordered by id ascending, so rows with id 3, 4, and 5 are returned.

3. Final Answer:

   Rows 3, 4, and 5 ordered by id ascending. -> Option C

4. Quick Check:

   OFFSET 2 skips first 2, LIMIT 3 returns next 3 [OK]

Hint: OFFSET skips rows, LIMIT picks next rows [OK]

Common Mistakes:

• Ignoring OFFSET and returning first rows
• Confusing OFFSET number with LIMIT
• Not applying ORDER BY before OFFSET

4. Identify the error in this query:

SELECT * FROM Products LIMIT 5 OFFSET;

medium

A. LIMIT cannot be used with OFFSET.

B. OFFSET requires a number after it.

C. SELECT * is not allowed with OFFSET.

D. OFFSET must come before LIMIT.

Solution

1. Step 1: Check OFFSET syntax

   OFFSET must be followed by a number indicating how many rows to skip.

2. Step 2: Analyze the query

   The query ends with OFFSET but no number is given, causing a syntax error.

3. Final Answer:

   OFFSET requires a number after it. -> Option B

4. Quick Check:

   OFFSET needs number [OK]

Hint: Always put a number after OFFSET [OK]

Common Mistakes:

• Leaving OFFSET without a number
• Thinking OFFSET must come before LIMIT
• Believing SELECT * disallows OFFSET

5. You want to display page 4 of a product list with 20 items per page, ordered by created_at descending. Which query correctly uses OFFSET and LIMIT?

hard

A. SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 60;

B. SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 80;

C. SELECT * FROM Products ORDER BY created_at DESC LIMIT 4 OFFSET 20;

D. SELECT * FROM Products ORDER BY created_at DESC OFFSET 20 LIMIT 4;

Solution

1. Step 1: Calculate OFFSET for page 4

   Each page has 20 items, so page 4 starts after 3 pages: 3 * 20 = 60 rows skipped.

2. Step 2: Check LIMIT and OFFSET usage

   LIMIT 20 returns 20 rows, OFFSET 60 skips first 60 rows, so page 4 shows rows 61-80.

3. Step 3: Verify ORDER BY

   ORDER BY created_at DESC ensures newest products first, consistent paging.

4. Final Answer:

   SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 60; -> Option A

5. Quick Check:

   Page 4 offset = 60, limit = 20 [OK]

Hint: OFFSET = (page-1)*page_size, LIMIT = page_size [OK]

Common Mistakes:

• Calculating OFFSET as page*page_size
• Swapping LIMIT and OFFSET values
• Omitting ORDER BY for consistent paging