Module 11 — LIMIT & OFFSET

1. LIMIT — Show Only N Rows

What it is: LIMIT restricts the number of rows returned by a query. Without it, a query returns all matching rows — which could be millions.

Why we use it: Applications display data in pages (pagination), show "top N" results, or need just a sample. LIMIT controls how much data is returned, improving performance and user experience.

When we use it: In paginated APIs, "top N" queries, random sampling, or any time you don't need all matching rows.

-- Show only the first 5 products
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

2. OFFSET — Skip Rows

What it is: OFFSET skips a specified number of rows before starting to return results. Combined with LIMIT, it implements pagination — showing a different "page" of results each time.

Why we use it: When a user clicks "Page 2" in a paginated list, OFFSET skips the rows from Page 1 and returns the next batch. This is the standard way to implement pagination in SQL.

When we use it: In paginated APIs, paginated UIs, or any time you need to access a specific "page" of results.

-- Skip the first 5, show the next 5 (Page 2)
SELECT * FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 5;
 
-- Skip the first 10, show the next 5 (Page 3)
SELECT * FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 10;

3. The Pagination Formula

What it is: The pagination formula calculates the correct LIMIT and OFFSET values for any page number. It's a simple mathematical relationship: LIMIT S OFFSET (N - 1) * S where S is items per page and N is the page number.

Why we use it: Every paginated API needs this formula to return the correct page. Without it, you'd have to manually calculate offsets for each page.

When we use it: In every paginated endpoint — user lists, product catalogs, search results, admin dashboards, etc.

To get Page N with S items per page:

LIMIT S OFFSET (N - 1) * S

Pagination Logic

4. LIMIT without OFFSET

What it is: Using LIMIT without OFFSET returns just the first N rows. This is simpler than full pagination — you're not skipping rows, just taking a fixed number from the top.

Why we use it: When you only need a subset — "top 3 products", "latest 10 posts", "5 random users". It's also useful for quick data sampling during development.

When we use it: In "top N" queries, random sampling, limiting API response sizes, or quick data exploration.

-- Get the top 3 most expensive products
SELECT * FROM products
ORDER BY price DESC
LIMIT 3;
 
-- Get a random sample of 5 products
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 5;

5. FETCH (SQL Standard Alternative)

What it is: FETCH FIRST N ROWS ONLY is the SQL standard equivalent of LIMIT. PostgreSQL supports both, but LIMIT is more commonly used in the PostgreSQL ecosystem.

Why we use it: If you're writing SQL that needs to work across multiple databases (PostgreSQL, MySQL, Oracle), FETCH is more portable. For PostgreSQL-only projects, LIMIT is preferred.

When we use it: When writing cross-database compatible SQL, or when following strict SQL standard conventions.

-- These are equivalent:
SELECT * FROM products ORDER BY price FETCH FIRST 5 ROWS ONLY;
SELECT * FROM products ORDER BY price LIMIT 5;

Performance Warning

-- Keyset pagination (much faster for large offsets)
-- Instead of: LIMIT 10 OFFSET 1000000
SELECT * FROM products
WHERE id > 1000000  -- last seen id
ORDER BY id
LIMIT 10;

Common Mistakes to Avoid

1. LIMIT without ORDER BY — Results are unpredictable between queries
2. Large OFFSET values — Performance degrades as offset grows
3. Forgetting to handle "no more pages" — Check if the result set is smaller than LIMIT

Practice Exercise

Using your courses table:

1. Select the 3 most expensive courses
2. Select the 2 cheapest courses
3. Implement pagination: get Page 2 with 2 items per page
4. Get a random course using ORDER BY RANDOM() LIMIT 1
5. What happens if you request Page 100 with 10 items per page when you only have 5 courses?