LIMIT & OFFSET
Control result set size and pagination
LIMIT restricts how many rows are returned. OFFSET skips rows. Together they enable pagination.
Basic LIMIT
-- Get first 10 rows SELECT * FROM users LIMIT 10; -- Top 5 most expensive products SELECT * FROM products ORDER BY price DESC LIMIT 5;
OFFSET for Skipping Rows
-- Skip first 10, get next 10 SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- Alternative syntax (MySQL, SQLite) SELECT * FROM users ORDER BY id LIMIT 10, 10; -- LIMIT offset, count
Pagination Example
-- 10 items per page -- Page 1: OFFSET 0 SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0; -- Page 2: OFFSET 10 SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10; -- Page 3: OFFSET 20 SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; -- Formula: OFFSET = (page_number - 1) * items_per_page
Database Variations
| Database | Syntax |
|---|---|
| SQLite, MySQL, PostgreSQL | LIMIT 10 OFFSET 20 |
| SQL Server | OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY |
| Oracle 12c+ | OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY |
Performance Warning: Large OFFSET values are slow because the database still reads and discards all skipped rows. For better pagination on large tables, use keyset pagination (WHERE id > last_seen_id).