The SQL – OFFSET clause is used in conjunction with the LIMIT clause to skip a certain number of rows before starting to return the rows. The basic syntax for the OFFSET clause is as follows:
SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET skip_rows;
For example, the following SQL query selects the first 10 rows from the employees table, starting from the fifth row:
SELECT first_name, last_name, salary
FROM employees
LIMIT 10 OFFSET 5;
In this example, the query selects the first_name, last_name, and salary columns from the employees table, but skips the first 5 rows, and returns only the next 10 rows.
Another example, the following SQL query selects the last 5 rows from the employees table, starting from the third row:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 3;
In this example, the query first sorts the employees by salary in descending order, but skips the first 3 rows, and then returns only the last 5 rows from the result set.
It’s important to note that the OFFSET clause should be used after the LIMIT clause and before any other clauses such as WHERE, JOIN, GROUP BY, and ORDER BY. Also, the behavior of the OFFSET clause can vary depending on the specific database management system you are using.
LIMIT vs OFFSET
The main difference between the LIMIT clause and the OFFSET clause in SQL is their function and how they are used in a query.
| LIMIT Clause | OFFSET Clause |
|---|---|
| The LIMIT clause is used to specify the maximum number of rows that a SELECT statement should return. | The OFFSET clause is used to specify the number of rows to skip before starting to return rows from a SELECT statement. |
| Syntax: SELECT column1, column2 FROM table_name LIMIT number_of_rows; | Syntax: SELECT column1, column2 FROM table_name OFFSET number_of_rows; |
| Example: SELECT * FROM employees LIMIT 5; | Example: SELECT * FROM employees LIMIT 5 OFFSET 2; |
| It is used to return a specific number of records. | It is used to skip a specific number of records. |
You can use these two clauses together to achieve pagination. Example: SELECT * FROM employees LIMIT 5 OFFSET 10; This query will return 5 records starting from the 11th record.