MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

SQL OFFSET: Skipping Rows in a Query

intro

Let's find out everything you need to know about the SQL OFFSET clause to start writing advanced pagination queries like a pro.

Today's data-driven applications rely on pagination to efficiently present large datasets to users. This technique allows seamless navigation between results, improving user experience and performance. To implement it, you must skip a specified number of records in query results. This is possible through the SQL OFFSET clause!

In this article, you will delve into OFFSET in SQL, discussing its syntax, use cases, and best practices.

Let’s master pagination in SQL!

What Is the SQL OFFSET Clause?

In SQL, the OFFSET clause is used to skip a specific number of rows before starting to return records from a query. That means specifying a starting point for row selection, which is useful for excluding a given number of records.

In databases that support the non-standard LIMIT clause, the OFFSET SQL keyword must be used together with LIMIT. For databases that adhere to the standard SQL ANSI specification, OFFSET can be used in conjunction with the FETCH clause.

In both cases, the SQL OFFSET clause is meant to paginate the results in a SELECT query. That makes it easier to retrieve subsets of data, such as the second page of results.

How to Use OFFSET in SQL

The way to use OFFSET in SQL changes from database to database. Time to dig into the OFFSET syntax in popular database engines!

MySQL

In MySQL, OFFSET is an optional clause you can add after LIMIT to skip a rows_to_skip number of records:

Copy
        
1 SELECT column_names 2 FROM table_name 3 [WHERE ...] 4 LIMIT row_count OFFSET rows_to_skip;

Where rows_to_skip must be a positive integer or 0. In particular, OFFSET 0 corresponds to omitting the MySQL OFFSET clause. OFFSET is not to be confused with LIMIT where 0 corresponds to the first row. However, OFFSET is often used together with LIMIT as you will see throughout this article.

Consider the MySQL sample OFFSET SQL query below:

Copy
        
1 SELECT * 2 FROM users 3 LIMIT 10 OFFSET 5;

This returns a maximum of 10 rows from the users table, skipping the first 5 rows in the result set. So, it selects rows 6 through 15 from users.

Note that the above query is equivalent to:

Copy
        
1 SELECT * 2 FROM users 3 LIMIT 5, 10;

This is another way of skipping rows in MySQL.

SQL Server

SQL Server follows the ANSI standard and does not support LIMIT. Instead, it adheres to the OFFSET-FETCH specification. Thus, the SQL Server OFFSET syntax is:

Copy
        
1 SELECT column_names 2 FROM table_name 3 [WHERE ...] 4 ORDER BY condition 5 OFFSET rows_to_skip ROWS 6 [FETCH ...];

As you can see, it must follow an ORDER BY clause and can be followed by an optional FETCH clause.

rows_to_skip must be an integer, otherwise, the query will fail with the following error:

Copy
        
1 [Code: 10743, SQL State: S1000] The number of rows provided for a OFFSET clause must be an integer.

OFFSET 0 ROWS corresponds to omitting the OFFSET clause.

Consider this sample SQL Server OFFSET query:

Copy
        
1 SELECT * 2 FROM Users 3 ORDER BY id 4 OFFSET 5 ROWS 5 FETCH NEXT 10 ROWS ONLY;

This retrieves 10 rows from the Users table, ordered by the id column, while skipping the first 5 rows.

PostgreSQL

PostgreSQL supports both a MySQL-like LIMIT syntax and a standard OFFSET-FETCH syntax. This is how you can use the SQL OFFSET clause in a PostgreSQL LIMIT query:

Copy
        
1 SELECT column_names 2 FROM table_name 3 [WHERE ...] 4 LIMIT row_count OFFSET rows_to_skip;

Where rows_to_skip must be an integer or NULL. In PostgreSQL, OFFSET 0 or OFFSET NULL corresponds to omitting the OFFSET SQL section from the query.

Equivalently, this is the PostgreSQL OFFSET-FETCH syntax:

Copy
        
1 SELECT column_names 2 FROM table_name 3 [WHERE ...] 4 OFFSET rows_to_skip [{ ROW | ROWS }] 5 [FETCH ...];

Compared to SQL Server, the ORDER BY clause is optional and rows_to_skip can be NULL. Also, the ROW or ROWS keyword can be omitted.

Here is a sample query to showcase the usage of OFFSET in PostgreSQL:

Copy
        
1 SELECT * 2 FROM users 3 LIMIT 10 OFFSET 5;

Similarly, you can write that query as:

Copy
        
1 SELECT * 2 FROM users 3 OFFSET 5 4 FETCH NEXT 10 ROWS ONLY;

Both queries retrieve 10 rows from the users table, while skipping the first 5 rows.

Oracle

Oracle follows the OFFSET-FETCH standard, providing the following syntax for the OFFSET clause:

Copy
        
1 SELECT column_names 2 FROM table_name 3 [WHERE ...] 4 OFFSET rows_to_skip [{ ROW | ROWS }] 5 [FETCH ...];

Technically, the SQL OFFSET clause can be used without an ORDER BY clause in Oracle. However, the results would be non-deterministic. That is why you should always specify them after ORDER BY.

Use Cases of the OFFSET SQL Clause

Let’s explore the best use cases of this clause in some SQL OFFSET query examples.

Pagination in Web Applications

The idea here is to display a list of elements (e.g., products, articles, blog posts) over multiple pages. This is called “pagination” and is a core UX pattern in SEO-oriented sites.

For example, you could load the products on the third 10-item page as below:

Copy
        
1 SELECT * 2 FROM products 3 ORDER BY id ASC 4 LIMIT 10 OFFSET 20;

Retrieving Through Large Data Sets

Large datasets are generally hard to explore, and it is better to navigate through them by selecting only a few records at a time. For example, you could view the next 100 records with:

Copy
        
1 SELECT * 2 FROM logs 3 ORDER BY timestamp ASC 4 OFFSET 1000 ROWS 5 FETCH NEXT 100 ROWS ONLY;

Infinite Scrolling

The SQL OFFSET clause supports the infinite scrolling pattern by allowing users to load portions of data as they scroll down.

For example, you could fetch a batch with the next 15 comments with the following query:

Copy
        
1 SELECT * 2 FROM comments 3 ORDER BY timestamp ASC 4 OFFSET 30 ROWS 5 FETCH NEXT 15 ROWS ONLY;

SQL OFFSET: Best Practices

Below are the main best practices for using OFFSET in SQL like a pro:

  • Ensure an ORDER BY clause is included to define the order of the rows and avoid inconsistent results.
  • Use OFFSET to limit the size of the result set, preventing sending too much data to the client to improve performance and user experience.
  • Try to always pair OFFSET with LIMIT or FETCH to control the number of rows returned.
  • Try to avoid high OFFSET values, as they can lead to poor performance, especially on large datasets.
  • On large datasets, consider using keyset pagination for more efficient pagination.

Conclusion

In this article, you understood what the OFFSET SQL clause is, how databases support it, and how to use it. Now you know that it helps you skip a specified number of rows in a query. Thanks to the use cases explored here, you also understood when to use OFFSET in SQL.

Complex data fetching becomes easier with a database client. DbVisualizer is a powerful database client that supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free today!

FAQ

Is SQL OFFSET part of the standard specification?

OFFSET is part of the standard SQL specification only in the OFFSET-FETCH syntax. However, it can also be used in conjunction with LIMIT in some SQL databases, such as PostgreSQL and MySQL. That syntax is not part of the ANSI-SQL standard.

How does the SQL Server OFFSET differ from the MySQL OFFSET?

The SQL Server OFFSET clause is generally used in conjunction with FETCH and requires an ORDER BY clause. In MySQL, OFFSET is used with LIMIT.

What is the difference between LIMIT and OFFSET in SQL?

In SQL, LIMIT defines the maximum number of rows to return from a query. Instead, OFFSET specifies the number of rows to skip before starting to return rows. In other words, LIMIT is used to restrict the result set, while OFFSET is used for pagination.

What is the difference between an OFFSET-FETCH query and a LIMIT OFFSET SQL query?

An OFFSET-FETCH query is an ANSI standard query used in SQL Server, PostgreSQL, and Oracle for pagination. In contrast, a LIMIT OFFSET SQL query is an equivalent non-standard query supported by databases like MySQL and PostgreSQL.

What are the performance considerations to take into account when using OFFSET in SQL?

When using OFFSET in SQL, consider that high skip values can lead to poor performance. That is because the database must scan and skip many rows. This can be mitigated by using proper indexing on the ordered columns, limiting the result set size.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

PostgreSQL Subquery - A Guide

author Leslie S. Gyamfi tags POSTGRESQL SUBQUERY 3 min 2024-10-02
title

A Complete Guide to the SQL Server FOR XML PATH Clause

author Antonello Zanini tags SQL SERVER XML 8 min 2024-10-01
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

A Guide to the Postgres Not Null Constraint

author Leslie S. Gyamfi tags POSTGRESQL 3 min 2024-09-25
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

A Complete Guide to the SQL Server COALESCE Function

author Antonello Zanini tags SQL SERVER 6 min 2024-09-23
title

SQL DATEPART: Get a Part of a Date in SQL Server

author Antonello Zanini tags SQL SERVER 8 min 2024-09-19
title

MySQL Binary Logs – Walkthrough

author Lukas Vileikis tags Binary Log MySQL 6 min 2024-09-18
title

MySQL SHOW TABLES Statement: What it is, How It Works, What It Means for You

author Lukas Vileikis tags MySQL 5 min 2024-09-16

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗