LIMIT and Pagination

LIMIT caps how many rows a query returns, regardless of how many actually match:

SELECT name FROM users ORDER BY signup_date DESC LIMIT 10;

This returns at most 10 rows — the 10 most recently signed-up users, since it's combined with ORDER BY. LIMIT without ORDER BY is rarely useful on its own, since "the first 10 rows" is meaningless without a defined order to be first in — as covered in the previous lesson, row order is otherwise unspecified.

OFFSET: skipping rows

OFFSET skips a number of rows before starting to return results, and combined with LIMIT, it's the standard building block for pagination:

-- Page 1 (rows 1-10)
SELECT name FROM users ORDER BY id LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT name FROM users ORDER BY id LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT name FROM users ORDER BY id LIMIT 10 OFFSET 20;

The general pattern for page n with pageSize rows per page is LIMIT pageSize OFFSET (n - 1) * pageSize.

A performance caveat with large offsets

OFFSET doesn't skip rows for free — the database typically still has to scan past every skipped row to reach the ones it returns. OFFSET 100000 on a large table is meaningfully slower than OFFSET 10, because the engine still walks through the first 100,000 matching rows before it can start returning the next page. For deep pagination on large tables, keyset pagination (also called cursor-based pagination) is the common fix: instead of an offset, you remember the last row's sort value and filter for rows after it.

-- Instead of OFFSET, remember the last id seen and filter past it
SELECT name, id FROM users WHERE id > 1043 ORDER BY id LIMIT 10;

This scales to any page depth at roughly constant speed, because it uses WHERE (which an index can jump straight to, as covered in a later lesson) instead of scanning and discarding rows.

Dialect differences

LIMIT/OFFSET is what MySQL, PostgreSQL, and SQLite use. SQL Server traditionally uses TOP n (with no direct OFFSET equivalent until OFFSET ... FETCH NEXT n ROWS ONLY was added), and the ANSI standard itself specifies FETCH FIRST n ROWS ONLY — another spot, as mentioned in the first lesson, where the "standard" and what a specific database actually accepts diverge.

Queries against a single table only get you so far — the next section covers JOIN, for combining data that's split across related tables.