LIMIT vs. FETCH in SQL

Fun fact: There is no limit clause in the SQL standard.

Everyone uses limit:

select * from employees
order by salary desc
limit 5;

And yet, according to the standard, we should be using fetch:

select * from employees
order by salary desc
fetch first 5 rows only;

fetch first N rows only does exactly what limit N does. But fetch can do more.

Limit with ties

Suppose we want to select the top 5 employees by salary, but also select anyone with the same salary as the last (5th) employee. Here comes with ties:

select * from employees
order by salary desc
fetch first 5 rows with ties;

Relative limit

Suppose we want to select the top 10% of employees by salary. percent to the rescue:

select * from employees
order by salary desc
fetch first 10 percent rows only;

┌────┬───────┬────────────┬────────┐
│ id │ name  │ department │ salary │
├────┼───────┼────────────┼────────┤
│ 25 │ Frank │ it         │ 120    │
│ 23 │ Henry │ it         │ 104    │
└────┴───────┴────────────┴────────┘

(there are 20 employees, so 10% is 2 records)

Offset with fetch

Suppose we want to skip the first 3 employees and select the next 5. No problem: fetch plays nicely with offset, as does limit:

select * from employees
order by salary desc
offset 3 rows
fetch next 5 rows only;

next here is just a syntactic sugar, a synonym for first in the previous examples. We can use first and get exactly the same result:

select * from employees
order by salary desc
offset 3 rows
fetch first 5 rows only;

Oh, and by the way, row and rows are also synonyms.

Database support

The following DBMS support fetch:

• PostgreSQL 8.4+
• Oracle 12c+
• MS SQL 2012+
• DB2 9+

However, only Oracle supports percent fetching.

MySQL and SQLite do not support fetch at all.

──

P.S. Interested in mastering modern SQL? Check out my book — SQL Window Functions Explained

★ Subscribe to keep up with new posts.