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:
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:
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:
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:
1
SELECT column_names
2
FROM table_name
3
[WHERE ...]
4
ORDER BY condition
5
OFFSET rows_to_skip ROWS
6
[FETCH ...];
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:
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:
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:
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:
1
SELECT *
2
FROM users
3
LIMIT 10 OFFSET 5;
Similarly, you can write that query as:
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:
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:
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:
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:
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:
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.
