Paginating Query Results with LIMIT and OFFSET

The Mechanics of Pagination: LIMIT and OFFSET

At its core, pagination involves two simple actions:

1. Skipping a certain number of rows (OFFSET).
2. Taking a specific number of rows after that (LIMIT).

Let's look at a visual representation of this process.

The LIMIT and OFFSET clauses are typically the very last parts of a SELECT statement.

As you've just read, the logic is straightforward. If you want to display 20 items per page:

• Page 1: You want to skip 0 rows and take 20.
  • LIMIT 20 OFFSET 0 (or just LIMIT 20, since OFFSET 0 is the default).
• Page 2: You want to skip the first 20 rows and take the next 20.
  • LIMIT 20 OFFSET 20.
• Page 3: You want to skip the first 40 rows and take the next 20.
  • LIMIT 20 OFFSET 40.

The general formula is OFFSET (page_number - 1) * page_size.

The Critical Role of ORDER BY

A crucial question arises: a "page" of data is only meaningful if the order of the data is consistent. If you ask for the second page of users, you expect it to contain the same users every time. How do we guarantee this?

This is where the previous lesson on ORDER BY becomes essential. Without an explicit ORDER BY clause, a relational database does not guarantee the order of rows returned. The order could change between queries based on internal optimization strategies.

Therefore, LIMIT and OFFSET should almost always be used with ORDER BY to ensure stable and predictable pagination.

Logically, this makes sense when you consider the SQL execution order. ORDER BY is processed, creating a sorted list. Only then, at the very end, do LIMIT and OFFSET take a slice of that final, sorted list.

SELECT
    product_name,
    price
FROM products
ORDER BY price DESC
LIMIT 15 OFFSET 45;

Syntax Across Different Database Systems

While the LIMIT/OFFSET syntax is common, especially in open-source databases like MySQL and PostgreSQL, it's not part of the official SQL standard. Other systems, like SQL Server and Oracle, use a different syntax to achieve the same result. Being aware of these differences is important for job readiness, as you may encounter different database backends.

To summarize the key variations:

• PostgreSQL / MySQL: LIMIT <row_count> OFFSET <offset>
• SQL Server / Oracle: OFFSET <offset> ROWS FETCH NEXT <row_count> ROWS ONLY

The Deeper Dive: Hidden Dangers of OFFSET

For simple applications or small tables, LIMIT/OFFSET works perfectly well. However, in high-traffic systems with large, frequently changing datasets, it has two serious drawbacks. This is the kind of "low-level" detail that separates basic knowledge from a professional understanding of system behavior.

1. Performance Degradation: As the OFFSET value grows, the query becomes slower. If you request OFFSET 1000000, the database must still fetch all 1,000,000 rows, sort them, and then discard them before it can even start picking the rows for your requested page. It doesn't just "jump" to the millionth row.
2. Data Instability: If new rows are inserted into the table while a user is clicking from page to page, the "window" of data shifts. This can cause the user to see duplicate records on a subsequent page or miss some records entirely.

This video provides an excellent demonstration of these problems.

The more robust alternative to OFFSET-based pagination is called keyset pagination (or "cursor-based" pagination). Instead of telling the database how many rows to skip, you tell it where to start the next page based on the last value seen on the previous page (e.g., "get me the next 20 rows after created_at = '2023-10-26 10:00:00'"). This is a more advanced topic, but it's valuable to know that a problem with OFFSET exists and that a better solution is used in high-performance systems.

Conclusion

You have now reached the end of Module 2 and have mastered the fundamental building blocks of querying a single table. You can select columns, filter rows with complex conditions, sort the results, and now, select specific pages of data.

Key Takeaways:

• LIMIT specifies the maximum number of rows to return, while OFFSET specifies how many rows to skip.
• Together, they are the standard mechanism for implementing pagination.
• Using LIMIT/OFFSET without ORDER BY leads to unpredictable results, as row order is not guaranteed.
• OFFSET-based pagination suffers from serious performance degradation on large page numbers and can show inconsistent data if the underlying table is being modified.
• Syntax for pagination can differ between database systems (e.g., MySQL vs. SQL Server).

Preview of the Next Module:
So far, all our queries have dealt with data from a single table. But the real power of a relational database comes from its ability to connect, or relate, data from multiple tables. How do you link a customer to all the orders they've placed? In our next module, "Joining Multiple Tables," we will start by exploring the foundational concepts that make these relationships possible: primary keys and foreign keys. This will open up a whole new level of querying capability.