Pagination in SQL

Pagination is an important part of database management, especially when working with large datasets. It helps in enabling efficient data retrieval by breaking down results into manageable loads. If you want to achieve effective pagination in SQL, then you first need to have a clear understanding of the underlying concepts and the use of syntax properly.

Pagination divides query results into smaller and more manageable record sets. Paginating results in SQL is more useful when negotiating with large datasets. Because users can view and navigate through results gradually. So, we use the ‘LIMIT‘ clause and the ‘OFFSET‘ clause for pagination in SQL.

Pagination Results in SQL

The main concept of the Paginating results in SQL involves the use of the ‘LIMIT‘ and ‘OFFSET‘ clauses in SQL queries. By the name of the clause, we can know that it simply limits the number of rows.

The ‘LIMIT’ clause is mainly used for restricting the number of rows that are returned in a set of results. Here the ‘OFFSET’ clause is to specify the starting point within the result set. Let’s see the syntax first and then I’ll explain the concept with some examples.

Syntax:

SELECT column1, column2, column3 ...
FROM table_name
LIMIT number_of_rows OFFSET offset_value;

Here the ‘number_of_rows‘ indicates the maximum number of rows to return and ‘offset_value‘ is the starting point within the result set.

Before we directly take examples of Pagination results in SQL, let me first create the table and insert the values in it so that we will get the accurate outputs as the results. So, you also make sure to create the proper table insert the correct values in your table, and then start paginating results.

Querry:

CREATE DATABASE company;

USE company;

CREATE TABLE employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(255),
    Salary INT
);

So, make sure when creating the table, you first specify the database that we are using that particular one. So, here I have successfully created our table named ‘employee‘. Let’s add the values in our table and then we’ll move to pagination and understand it with different examples.

INSERT INTO employees (ID, Name, Salary) VALUES
(1, 'Ram', 50000),
(2, 'Sita', 60000),
(3, 'Mohan', 55000),
(4, 'Ishan', 70000),
(5, 'Bindu', 48000),
(6, 'Disha', 62000),
(7, 'Nita', 58000),
(8, 'Dipa', 53000),
(9, 'Nitish', 56000),
(10, 'Sagar', 59000),
(11, 'Sara', 58000),
(12, 'Rishi', 62000),
(13, 'Golu', 54000),
(14, 'Tanisq', 58000),
(15, 'Arjun', 54000);

So, these are the values that are now added to our ‘employee’ table. Let’s start with our first example.

Example 1: Basic Pagination

SELECT * FROM employees
LIMIT 10;

Output:

Basic Pagination

Don’t get confused by the ‘*’ operator, it means that we want all the columns. When I used the ‘LIMIT’ clause, it restricted the number of rows to 10, it returned the 10 rows out of the 15 rows as expected.

Example 2: Pagination with Offset

SELECT * FROM employees
LIMIT 5 OFFSET 10;

Output:

Pagination with Offset

In this example, I have used both the ‘LIMIT‘ and the ‘OFFSET‘ clauses together. So, first the ‘LIMIT‘ clause restricted the number of rows to 5. And as we know, ‘OFFSET‘ clause is the starting point. So, you can see in the output image, that it returned the rows from 11 to 15.

Example 3: Advanced Pagination with Sorting

SELECT * FROM employees
ORDER BY Salary DESC
LIMIT 5 OFFSET 5;

Output:

Advanced Pagination with Sorting

The ‘ORDER BY‘ clause makes it even more interesting. Here I wanted to get the rows in descending orders, so by using the ‘DESC‘ clause, I can get the desired output. So, it retrieved 5 records starting from the 6th record and sorted by salary in descending order as I wanted.

Example 4: Cursor pagination

Cursor pagination is a method that is used to paginate through a large set of results in a database with the use of a cursor or pointer to keep track of the current position in the result set. This pagination is different from the traditional offset-based pagination. It provides more efficient and scalable performance, especially when working with large datasets. Cursor Pagination uses a unique identifier from the last fetched record for fetching the next set of results.

SET @lastEmployeeID = 5;

SELECT * FROM employees
WHERE ID > @lastEmployeeID
ORDER BY ID
LIMIT 5;

Output:

Cursor Pagination

Here I assumed the last fetched employee ID and set it to 5. I wanted to achieve Cursor Pagination here, and for that, I used ‘WHERE‘ clause directly. Here I’m using MySQL, that’s why I used the ‘SET‘ statement to assign a value to the variable. But if you’re using a different database, like SQL Server or PostgreSQL, then you can use the ‘DECLARE‘ statement to achieve the same result.

Conclusion

Pagination is a necessary method in SQL for managing large datasets effectively. By using the ‘LIMIT’ and ‘OFFSET‘ clauses, we can control the number of rows returned and navigate through result sets efficiently. Remember one thing effective pagination enhances user experience and optimizes query performance. It makes it a valuable skill for SQL developers and database administrators. Also, as I said above, you first need to check which database you’re working with, and according to that, you need to adjust the syntax based on the specific database system to get the desired output. So, before directly copying and pasting, check the environment and the query syntax once.