OFFSET in SQL with Examples

The OFFSET clause in SQL is used to skip a specific number of rows that come before returning the result set of a query. Instead of always starting from the very first row in that table, OFFSET allows you to dictate where the retrieval begins. We can easily see how this is valuable when working with tables that are large in size, and you want to add features like paging through the data. OFFSET is almost always used with the LIMIT clause so that you can skip some rows and then get only the next set of rows. In this blog, you will learn what OFFSET is in SQL, how it works with LIMIT, the syntax, use cases, and best practices.

Table of Contents:

• What is OFFSET in SQL?
• Syntax of SQL OFFSET Clause
• Using OFFSET and LIMIT in SQL
  1. SQL OFFSET with ORDER BY Clause
  2. SQL OFFSET with WHERE Clause
  3. SQL OFFSET in Different Databases
• Advanced Use Cases of OFFSET in SQL
• Benefits of Using OFFSET in SQL
• Challenges of OFFSET in SQL
• SQL OFFSET vs LIMIT vs FETCH NEXT
• Best Practices for OFFSET in SQL Queries
• Practical Use Cases of OFFSET in SQL
• Conclusion

What is OFFSET in SQL?

In SQL, the OFFSET clause is used to skip over a specific number of rows before starting to display the results. OFFSET is typically used in conjunction with a LIMIT clause when working with larger datasets to make the most efficient use of the information available. However, OFFSET is very useful for working with smaller data portions.

Key Features of OFFSET in SQL:

1. Row Skipping: OFFSET offers the ability to skip a specified number of rows before displaying results, which is useful when working with large tables.
2. Works with LIMIT: Many database systems (RDBMS) allow OFFSET to be used in conjunction with LIMIT to limit the number of returned rows after skipping specified rows.
3. Supports Pagination: OFFSET is crucial for pagination, which helps the user view the data more easily across the pages.
4. Improved Display of Data Locations: OFFSET provides the ease of jumping to locations where data exists without needing to load up the entire table.
5. Flexible Data Handling: OFFSET allows the developers to have better control over the data when paired with filtering and ordering.

Master SQL – From Beginner to Pro

Interactive lessons, real-world projects, live Q&A, and a certificate to showcase your skills.

Explore Program

Syntax of SQL OFFSET Clause

The OFFSET clause in SQL is written at the end of the query to specify the number of rows that should be skipped before returning the output.

Syntax:

SELECT column_name1, column_name2, ...
FROM table_name
OFFSET number;

Explanation:

• SELECT column_name1, column_name2,… is used to choose the columns you want to display from the table.
• FROM table_name tells SQL from which table to select the data.
• OFFSET number tells SQL how many rows to skip before it starts returning the result set.

NOTE: Let’s create a sample table that we will be using throughout this blog to understand the working of the SQL OFFSET clause in SQL.

-- Creating the courses_enrollment table
CREATE TABLE courses_enrollment (
    enrollment_id INT,
    student_name VARCHAR(50),
    course_title VARCHAR(50),
    enrollment_date DATE
);

-- Inserting sample data
INSERT INTO courses_enrollment (enrollment_id, student_name, course_title, enrollment_date) VALUES
(1, 'Rahul', 'Machine Learning', '2024-01-15'),
(2, 'Priya', 'Data Analytics', '2024-02-10'),
(3, 'Suresh', 'Web Development', '2024-03-05'),
(4, 'Neha', 'Database Management', '2024-03-12'),
(5, 'Rohan', 'Cloud Architecture', '2024-04-01'),
(6, 'Divya', 'Python Programming', '2024-04-15'),
(7, 'Aakash', 'Data Visualization', '2024-05-02'),
(8, 'Kavita', 'DevOps Fundamentals', '2024-05-20'),
(9, 'Manoj', 'Big Data', '2024-06-10'),
(10, 'Snehal', 'Artificial Intelligence', '2024-06-25');

--Display the data
Select * From courses_enrollment

Output:

Using OFFSET and LIMIT in SQL

The SQL offset clause works with the limit clause for controlling how many rows will be returned by the query. The LIMIT determines how many rows should be displayed, and the OFFSET decides how many rows the result set should skip before returning the result. This is useful for returning a set of data split up into multiple pages, where only a portion of the data set is displayed at a time.

Example:

-- Fetch 3 records starting from the 4th row
SELECT enrollment_id, student_name, course_title
FROM courses_enrollment
ORDER BY enrollment_id
LIMIT 3 OFFSET 3;

Output:

Explanation: Here, the query skips the first three rows and then displays the records in the next 3 rows. The LIMIT clause defines how many rows to fetch, while the OFFSET sets the starting point from which the records have to be fetched.

SQL OFFSET with ORDER BY Clause

Using the OFFSET with the ORDER BY clause, the query first sorts the rows by the chosen column and then skips the given number of rows before the result is displayed. This helps in making pagination simpler and reliable, and ensures that no rows are repeated across different pages. This makes pagination simple and reliable, as the order ensures no rows are repeated or missed across the pages.

Example:

SELECT enrollment_id, student_name, course_title, enrollment_date
FROM courses_enrollment
ORDER BY enrollment_date ASC
LIMIT 3 OFFSET 5;

Output:

Explanation: Here, the query arranges the rows by enrollment_date in ascending order and skips the first 5 rows. Then the next 3 rows make the pagination consistent.

SQL OFFSET with WHERE Clause

Using the OFFSET in SQL with the WHERE clause first filters the rows based on the specified condition, and then it skips the requested number of rows before displaying the results. Therefore, it can be used to retrieve a portion of the filtered data and facilitate pagination.

Example:

SELECT enrollment_id, student_name, course_title, enrollment_date  
FROM courses_enrollment  
WHERE course_title = 'Machine Learning'  
LIMIT 3 OFFSET 0;

Output:

Explanation: Here, the query filters the rows where the course_title is ‘Machine Learning’ and zero rows are skipped. Then the 3 rows from the filtered result set are displayed.

SQL OFFSET in Different Databases

The OFFSET clause in SQL is not supported in all databases. Let’s now understand how it works in different databases and their alternatives for the databases that don’t support it.

1. OFFSET in MySQL

In MySQL, the OFFSET clause is used together with the LIMIT clause. It skips the specified number of rows and then fetches the next set of results. This is especially useful for pagination, such as displaying results across multiple pages.

Example:

SELECT enrollment_id, student_name, course_title
FROM courses_enrollment
ORDER BY enrollment_date ASC
LIMIT 3 OFFSET 2;

Output:

Explanation: Here, the query skips the first 2 rows from the result and displays the next 3 according to the enrollment_date in ascending order

2. OFFSET in PostgreSQL

In PostgreSQL, the OFFSET clause is used together with LIMIT to skip a certain number of rows and then return a set number of rows. This is helpful when paginating data, for displaying data page by page.

Example:

SELECT enrollment_id, student_name, course_title
FROM courses_enrollment
ORDER BY enrollment_date ASC
OFFSET 2 LIMIT 3;

Output:

Explanation: Here, the query first skips the 2 rows after sorting according to the enrollment_date and then displays the next 3 rows starting from the 3rd row.

Note: Oracle and SQL Server do not support the SQL OFFSET clause. In an Oracle database, a similar function can be executed using ROWNUM or using the first FETCH FIRST n ROWS ONLY. In SQL Server, the OFFSET should always be used with an ORDER BY clause, and it must be used with FETCH NEXT for skipping rows and returning the next set of results.

Advanced Use Cases of OFFSET in SQL

Now, let’s explore some of the advanced use cases of SQL OFFSET.

1. OFFSET and FETCH NEXT in SQL

The OFFSET keyword is used together with FETCH NEXT in SQL Server to get a specified number of rows after an offset of a specified number of rows. OFFSET and FETCH NEXT are quite useful for pagination, either for applications or reports.

Example:

SELECT enrollment_id, student_name, course_title
FROM courses_enrollment
ORDER BY enrollment_date ASC
OFFSET 2 ROWS
FETCH NEXT 3 ROWS ONLY;

Output:

Explanation: Here, the query ignores the top 2 rows according to the enrollment_date and fetches the next 3 rows. By using OFFSET with FETCH NEXT, the data is consistently paged in SQL Server.

2. OFFSET with JOIN Queries

OFFSET can also be used on a query with a JOIN, and this can help skip rows after joining multiple tables of data. It allows you to be very precise in getting the result set from complex queries.

Example:

-- MySQL query with JOIN, LIMIT and OFFSET
SELECT ce.enrollment_id, ce.student_name, ce.course_title, cd.course_duration
FROM courses_enrollment ce
JOIN course_details cd
ON ce.course_title = cd.course_title
ORDER BY ce.enrollment_date ASC
LIMIT 2 OFFSET 3;

Output:

Explanation: Here, the query joins the tables courses_enrollment and course_details and returns the results ordered by the enrollment_date. It will skip the first three rows and then fetch the next two rows.

Benefits of Using OFFSET in SQL

1. Efficient Pagination: OFFSET allows for to display of only the portion of rows that is required out of a large amount of data, making it ideal for data display in a paged manner in apps and reports.
2. Improved Data Control: It permits the developer to skip rows before getting the desired portion of the data.
3. Works with LIMIT/FETCH NEXT: When you use LIMIT (MySQL/PostgreSQL) or FETCH NEXT (SQL Server) with OFFSET, it allows you to have a lot of control over the rows that are returned.
4. Better User Experience: Paginating data gives users a better experience because it does not overwhelm them with too much information at once.
5. Flexible Integration: It combines very easily with ORDER BY and WHERE clauses, making pagination while filtering or sorting data easy.

Challenges of OFFSET in SQL

1. Delayed Performance on Big Datasets: Using a high OFFSET can slow down queries because the database still needs to process rows that are skipped.
2. Must Use ORDER BY for Consistency: SQL OFFSET alone may return different results when executing the query several times. Hence, using ORDER BY along with OFFSET helps in getting accurate results when executing the same query many times.
3. Cannot be Used Alone on Some Databases: OFFSET must always be used in conjunction with ORDER BY and FETCH NEXT in SQL Server, and cannot be used alone.
4. Partial View of Data: OFFSET provides a partial view of data, so users are likely to lose important information, especially if not used along with filters.
5. Complicated Pagination Logic: With extremely large datasets, the method of OFFSET pagination can complicate performance and row ordering, requiring additional logic to be implemented.

SQL OFFSET vs LIMIT vs FETCH NEXT

Best Practices for OFFSET in SQL Queries

1. Always Use ORDER BY: Always use OFFSET with an ORDER BY clause to get consistent results. If you do not provide an ORDER BY clause, the order of rows returned may vary with each query during execution.
2. Use with LIMIT / FETCH NEXT: When the OFFSET is used alone, it will only skip rows. To control the number of rows returned, always use it with LIMIT in MySQL/PostgreSQL or FETCH NEXT in SQL Server.
3. Avoid using High OFFSET values on Large Tables: Using high OFFSET values can slow down queries as the database processes even the skipped rows.
4. Use Indexes for Better Performance: When the OFFSET is used along with the ORDER BY clause, it must be ensured that the columns used in the ORDER BY clause are properly indexed.
5. Combine with Filtering Conditions: Using the WHERE clause along with the OFFSET helps in retrieving only the relevant data, which reduces the time required for scanning and improves performance

Practical Use Cases of OFFSET in SQL

1. Pagination in Web Applications: The OFFSET in SQL helps in displaying the records page by page, such as showing only 10 records at a time in a search query.
2. Skipping Rows in Large Data Sets: The SQL OFFSET allows for skipping a particular number of rows, which helps in analyzing and reviewing the data in parts.
3. Infinite Scrolling Examples: OFFSET helps in continuously loading the data as the user scrolls down. It is commonly used in social media feeds and e-commerce pages.

Kickstart Your SQL Journey – 100% Free

Structured lessons, real query practice, and solid foundations at zero cost.

Explore Program

Conclusion

The OFFSET clause in SQL is extremely helpful when working with large amounts of data. OFFSET allows skipping certain rows and retrieving only the rows that are needed. This property makes it very useful for real-world use cases such as pagination, endless scrolling, and skipping unnecessary rows when generating a report. When OFFSET is used with LIMIT or FETCH NEXT, it provides much better control over the data that is returned. However, OFFSET can affect performance when not used correctly. To avoid slow queries with OFFSET, it is a good practice to index the columns in your ORDER BY clause and apply filtering conditions when possible.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

OFFSET in SQL with Examples – FAQs