Pagination in SQL Server, MySQL and Oracle with Examples: Often, while working with the application with a huge result set in the backend, it is not desired to display all the records on one page, and instead, it is always better to display them on multiple pages.

Also See: Structure of DBMS

Definition: Pagination is essentially dividing the large dataset into multiple pages or it is equally distributing the result set in multiple pages.

Pagination in SQL Server, MySQL and Oracle with Examples

Here, SQL returns the data page by page where page size can be set as required. Most web developers implement pagination in their applications especially when dealing with large datasets, thereby increasing the readability of the result set.

Also See: Schedule in DBMS

Pagination in MYSQL

We can implement pagination in MySQL by using two keywords LIMIT and OFFSET in the select query as shown below.

Syntax:

Select * from <tableName> LIMIT value1, OFFSET value2

Let us proceed to know more details of LIMIT, OFFSET clauses.

LIMIT – This clause is used to restrict the total number of rows to be returned via the SQL query.

For example, we have a database named world with table city which has columns like id, country code, name, population, etc as shown below.

Pagination in MYSQL

But you want to restrict it to 15 records only. In other words, you only want to display the first 15 records, in that case, you can use the limit clause as shown below.

Select * from world.city LIMIT 15;

Output of the query is:

Another syntax you can use in MySQL is

Select * from <tableName> LIMIT value1, value2

where value1 indicates the first record to be selected and value2 indicates the count of records starting from value1.

Also See: Decomposition in DBMS

For example,

Select * from world.city LIMIT 15,5;

In this case, value1 is 15 and value2 is 5. Thereby, it starts from the 16th record and displays up to 5 records as shown below

Output of the query is:

OFFSET: This clause is used to view the specific number of rows.

For example, we want to view records from 21 to 30 then we can use offset it. It basically discards all the previous records that have been mentioned in the OFFSET clause and limits to the records that have been mentioned in LIMIT clause.

Select * from world.city LIMIT 10 OFFSET 20;

In this case, it offsets 20 records, thereby starts from 21st and displaying all the 10 records including it.

Also See: Serializability in DBMS

Output of the query is:

Pagination in MYSQL 3Performance overhead:

Pagination is a great way of displaying large results set into multiple pages but then sometimes there is additional overhead on the database server when sharing the same server among multiple clients. To avoid performance issues, we can use the below approach which eliminates the discarding of records.

We can simply eliminate the use of OFFSET clause and rather use LIMIT with the where clause.  We specify the range of records within the where clause and restrict the records using limit clause.

Also See: SQL Operators

You can use the below query which ultimately produces the same output as shown earlier.

Select * from world.city where id > 30  LIMIT 10;

Output of the query is:

Pagination in MYSQL 4

Also See: SQL – INSERT INTO STATEMENT

Pagination in SQL server

The pagination concept remains the same as that explained in the previous section. However, as we know every database has its own syntax, implementation of pagination in SQL server has been given below.

Keywords used are OFFSET, FETCH with the ORDER BY in the SELECT statement

SELECT column1, column2 FROM <TableName> ORDER BY column2 OFFSET VALUE1 ROWS
  • For Example, if you want to discard first 5 rows and display all the remaining rows then you will use the query as
SELECT column1, column2 FROM <TableName> ORDER BY column2 OFFSET 5 ROWS

If you want to discard no row, simply mention the offset value as 0 as shown below.

SELECT column1, column2 FROM <TableName> ORDER BY column2 OFFSET 0 ROWS
  • If you want to specify how many records will be displayed after skipping some of the records then FETCH clause comes into picture along with OFFSET. The syntax is as shown below
SELECT column1, column2 FROM <TableName> ORDER BY column2 OFFSET value1 ROWS FETCH NEXT value2

ROWS ONLY

For example, you want to discard first 5 records and then display upto 7 records from there, you can use the below query

SELECT column1, column2 FROM <TableName> ORDER BY column2 OFFSET 5 ROWS FETCH NEXT 7

ROWS ONLY

Also See: DATA TYPES IN SQL

Pagination in Oracle

To implement pagination in oracle, you can use row_number() method that is been especially used to provide a unique row number to each of the records in the table of the oracle database.

As per below query, 5 records will be displayed.

SELECT * FROM (

    SELECT

      ord.*,

      row_number() over (ORDER BY ord.order_id ASC) line_number FROM Orders ord  ) WHERE line_number

BETWEEN 0 AND 5  ORDER BY line_number;

Apart from above syntax, oracle 12C also allows us to use LIMIT and OFFSET for pagination

Also See: SQL Vs MySQL

For example, you have a table which from which data needs to be fetched on the basis of the column in descending order using pagination.

Say,

page_size:=10

select * from tableName

order by columnName desc

OFFSET nvl(page_no-1,1) * page_size ROWS FETCH NEXT page_size ROWS ONLY;

For page 1, if page size is 10, OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY – This statement fetches 1st 10 rows only

For page 2 and page size=10, OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY – This statement fetches fetch next 10 rows.

Also See: SQL Server CONSTRAINTS With Examples

Sumit ThakurWhat Is DBMSPagination in SQL Server, MySQL and Oracle with Examples: Often, while working with the application with a huge result set in the backend, it is not desired to display all the records on one page, and instead, it is always better to display them on multiple pages. Also See: Structure...Let's Define DBMS