LIMIT and Result Set Pagination
What you'll learn: How to control the number of rows returned by a query and implement basic pagination using LIMIT and OFFSET.
The Core Idea
When querying a database, you often don't want all the results at once—especially when a table contains thousands or millions of rows. The LIMIT clause restricts how many rows are returned, while OFFSET lets you skip a certain number of rows before starting to return results. Together, they enable pagination: breaking large result sets into manageable "pages."
Think of it like reading a book. You don't read all 500 pages at once; you read page 1, then page 2, and so on. LIMIT is like saying "show me 10 lines," and OFFSET is like saying "but start from line 20."
Basic Syntax
SELECT * FROM users
ORDER BY username
LIMIT 10;
This returns only the first 10 users (when sorted by username).
Adding Pagination with OFFSET
SELECT * FROM users
ORDER BY username
LIMIT 10 OFFSET 20;
This skips the first 20 users and returns the next 10—effectively giving you "page 3" if each page shows 10 users (page 1: rows 0-9, page 2: rows 10-19, page 3: rows 20-29).
Why ORDER BY Matters
Without ORDER BY, the database can return rows in any order, making pagination unpredictable. Always sort your results before paginating to ensure consistent "pages."
Calculating Offset for a Given Page
For page number P with N results per page:
LIMIT NOFFSET (P - 1) * N
Key Takeaway: Use LIMIT to restrict result size and combine it with OFFSET to implement pagination, always with ORDER BY for consistent results.