Why OFFSET for pagination in SQL? - Purpose & Use Cases

The Big Idea

What if you could jump straight to the exact page you want without flipping through every single item first?

The Scenario

Imagine you have a huge list of books and you want to show only 10 books per page on your website. Without a tool to jump to the right spot, you would have to scroll through or count all the books from the start every time.

The Problem

Manually counting or filtering rows is slow and tiring. It's easy to make mistakes, like skipping or repeating books. Also, it wastes time and computer power because you look at all the data even if you only want a small part.

The Solution

The OFFSET feature lets you skip a set number of rows and start showing results from there. This means you can quickly jump to page 3, page 4, or any page without looking at all the previous data again.

Before vs After

✗ Before

SELECT * FROM books WHERE id > 20 LIMIT 10;

✓ After

SELECT * FROM books LIMIT 10 OFFSET 20;

What It Enables

OFFSET makes it easy to build fast and user-friendly pages that show data in small chunks, like turning pages in a book.

Real Life Example

When you browse an online store, OFFSET helps show you products page by page without loading the entire catalog at once.

Key Takeaways

Manual scrolling or filtering is slow and error-prone.

OFFSET skips rows to jump directly to the desired page.

This makes pagination efficient and user-friendly.