LIMIT and OFFSET for pagination in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LIMIT clause do in a SQL query?

The LIMIT clause restricts the number of rows returned by the query to a specified number.

beginner

How does OFFSET work in a SQL query?

OFFSET skips a specified number of rows before starting to return rows from the query result.

intermediate

Write a SQL query to get 5 rows starting from the 11th row in a table named 'products'.

SELECT * FROM products LIMIT 5 OFFSET 10;

beginner

Why is LIMIT and OFFSET useful for pagination?

They allow you to fetch a small part of the data at a time, like pages in a book, making it easier to display and manage large data sets.

beginner

What happens if you use LIMIT without OFFSET?

The query returns the first set of rows up to the LIMIT number, starting from the beginning of the result set.

What does the SQL clause OFFSET 20 do?

A Skips the first 20 rows

B Limits the result to 20 rows

C Returns rows starting from the 21st row

D Returns the first 20 rows

OFFSET skips the first 20 rows before returning results.

Which SQL clause limits the number of rows returned?

A LIMIT

B OFFSET

C WHERE

D GROUP BY

LIMIT restricts the number of rows returned by the query.

How do you get rows 11 to 20 from a table named 'users'?

A SELECT * FROM users OFFSET 10 LIMIT 10;

B SELECT * FROM users LIMIT 20 OFFSET 10;

C SELECT * FROM users LIMIT 10 OFFSET 11;

D SELECT * FROM users LIMIT 10 OFFSET 10;

LIMIT 10 OFFSET 10 skips first 10 rows and returns next 10 rows (11 to 20).

What is the default OFFSET if it is not specified?

A 1

B 0

C LIMIT value

D No rows are skipped

If OFFSET is not specified, it defaults to 0, meaning no rows are skipped.

Why might you use LIMIT and OFFSET together?

A To filter rows by a condition

B To sort rows alphabetically

C To paginate results by showing a subset of rows

D To join two tables

LIMIT and OFFSET together help show a specific page of results by selecting a subset of rows.

Explain how LIMIT and OFFSET work together to paginate query results.

Think of pages in a book and how you skip pages to get to the one you want.

Write a SQL query to fetch the third page of results if each page shows 15 rows.

Page 3 means skip first 30 rows (2 pages * 15 rows).