Difference between LIMIT N and FETCH NEXT N ROWS?

The two queries below seem equivalent. Is one better than the other? e.g. in terms of performance.

select * from some_table offset 10 limit 10;

select * from some_table offset 10 fetch next 10 rows;

Is one better than the other? e.g. in terms of performance.

No, they just differ in syntax.

Both achieve pagination, with the same exact performance.

The first statement uses the LIMIT clause, which is not part of standard SQL and originates as a vendor SQL extension. The second statement uses the FETCH clause, which was first introduced in the official SQL:2008 standard.

With links to official documentation, here's a table showing support for each statement in various major database engines:

Database Engine
First statement (LIMIT)
Second statement (FETCH NEXT)

MySQL 8.0.33
Supported ✅
Not supported ❌

PostgreSQL 15.3
Supported ✅
Supported ✅

Microsoft SQL Server 2022
Not supported ❌
Supported ✅

Oracle 19c
Not supported ❌
Not supported ❌

SQLite 3.42.0
Supported ✅
Not supported ❌

You can validate the performance aspect yourself using a sample PostgreSQL database and comparing the execution plans:
CREATE TABLE some_table (
id SERIAL PRIMARY KEY,
column1 TEXT,
column2 INTEGER,
column3 FLOAT,
column4 DATE
);

INSERT INTO some_table (column1, column2, column3, column4)
SELECT
md5(random()::text)::TEXT,
floor(random() * 100)::INTEGER,
random()::FLOAT,
CURRENT_DATE - (random() * 365)::INTEGER
FROM generate_series(1, 100);

EXPLAIN ANALYZE SELECT * FROM some_table OFFSET 10 LIMIT 10;
EXPLAIN ANALYZE SELECT * FROM some_table OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;


The output will show the exact same execution plan for both:
postgres=# EXPLAIN ANALYZE SELECT * FROM some_table OFFSET 10 LIMIT 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.30..0.60 rows=10 width=53) (actual time=0.014..0.015 rows=10 loops=1)
-> Seq Scan on some_table (cost=0.00..3.00 rows=100 width=53) (actual time=0.011..0.012 rows=20 loops=1)
Planning Time: 2.478 ms
Execution Time: 0.044 ms
(4 rows)

postgres=# EXPLAIN ANALYZE SELECT * FROM some_table OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.30..0.60 rows=10 width=53) (actual time=0.012..0.013 rows=10 loops=1)
-> Seq Scan on some_table (cost=0.00..3.00 rows=100 width=53) (actual time=0.010..0.011 rows=20 loops=1)
Planning Time: 0.051 ms
Execution Time: 0.020 ms
(4 rows)


The first one is visualised here and the second one is visualised here.

StackExchange Database Administrators Q#231050, answer score: 3