Implementing SQL Pagination with OFFSET and LIMIT

SELECT
    id,
    product_name,
    price
FROM
    products
ORDER BY
    product_name ASC
LIMIT 10 OFFSET 0; -- First page (10 records, starting from the 0th offset)

SELECT
    id,
    product_name,
    price
FROM
    products
ORDER BY
    product_name ASC
LIMIT 10 OFFSET 10; -- Second page (10 records, starting after the first 10)

-- General form:
-- SELECT columns FROM table ORDER BY column LIMIT :page_size OFFSET (:page_number - 1) * :page_size;

How it works: This essential SQL snippet demonstrates how to implement pagination, a crucial feature for web applications displaying large datasets. The `LIMIT` clause specifies the maximum number of rows to return (e.g., items per page), while the `OFFSET` clause indicates how many rows to skip from the beginning of the result set. By adjusting the `OFFSET` based on the current page number and page size, you can efficiently retrieve specific pages of data, improving performance and user experience. Remember to always use an `ORDER BY` clause with `LIMIT` and `OFFSET` to ensure consistent results.