LIMIT and OFFSET

LIMIT restricts the number of rows returned. OFFSET skips rows. Together, they enable pagination.

LIMIT Syntax

SELECT * FROM users LIMIT 5;

Returns only the first 5 rows.

OFFSET Syntax

SELECT * FROM users LIMIT 5 OFFSET 2;

Skips the first 2 rows, then returns 5 rows.

Exercises

Order Matters!

The query clause order is:

1. SELECT
2. FROM
3. WHERE
4. ORDER BY
5. LIMIT
6. OFFSET

Pagination Formula

For pagination with page_size items per page:

• Page 1: LIMIT page_size OFFSET 0
• Page 2: LIMIT page_size OFFSET page_size
• Page N: LIMIT page_size OFFSET (N-1) * page_size

Free Practice