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.