LIMIT & OFFSET
In this page:
SELECT column1, column2
FROM table_name
LIMIT row_count [OFFSET skip_rows];Limiting Your Results
Sometimes your queries match thousands of rows, but you only need to show a handful at a time. We can use the LIMIT clause to cap the maximum number of rows returned. This keeps our queries fast and manageable, especially on large tables.
Example: Limiting Your Results
SELECT * FROM products LIMIT 5;Skipping Records with OFFSET
The OFFSET clause tells MySQL to skip a specific number of rows before it starts returning results, such as skipping the first 20 rows to show page two. It is always used together with the LIMIT clause to define both where to start and how many rows to return.
Example: Skipping Records with OFFSET
SELECT * FROM products LIMIT 20 OFFSET 20;The Short Hand Limit Syntax
MySQL has a shorthand way to write both LIMIT and OFFSET in one line. You write LIMIT, then the offset number, a comma, and finally the row count limit, e.g. LIMIT 20, 10 skips 20 rows and returns the next 10.
Example: The Short Hand Limit Syntax
SELECT * FROM products LIMIT 20, 20;Creating Pages of Results
LIMIT and OFFSET are the keys to building database pagination for things like search results or product listings. By changing the offset based on the page number, we can display data in structured pages without loading the entire table at once.
Example: Creating Pages of Results
SELECT * FROM products LIMIT 10 OFFSET 20; -- page 3, 10 per pageMixing Filters Sorts and Limits
You can combine WHERE, ORDER BY, and LIMIT in a single query to filter, sort, and cap results together. The rules of order are strict: write WHERE first, then ORDER BY, and place LIMIT at the very end, or MySQL will raise a syntax error.
Example: Mixing Filters Sorts and Limits
SELECT * FROM products WHERE category = 'Books' ORDER BY price DESC LIMIT 5;- Using
LIMITwithoutORDER BY, so the rows returned are not predictable. - Miscalculating the offset for a page, when page
nneedsOFFSET (n - 1) * page_size. - Writing
LIMIT 20, 20and confusing it withLIMIT 20 OFFSET 20, when in the shorthand the offset comes first.
- The
WHEREclause filters rows, combined withAND,OR,NOT,IN,BETWEEN, andLIKE. IS NULLandIS NOT NULLtest for missing values.ORDER BYsorts results, andLIMITwithOFFSETreturns a specific portion of them.
Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: