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;

┌────┬───────┬────────────┬────────┐
│ id │ name  │ department │ salary │
├────┼───────┼────────────┼────────┤
│ 25 │ Frank │ it         │ 120    │
│ 23 │ Henry │ it         │ 104    │
│ 24 │ Irene │ it         │ 104    │
│ 33 │ Alice │ sales      │ 100    │
│ 31 │ Cindy │ sales      │ 96     │
└────┴───────┴────────────┴────────┘

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;

┌────┬───────┬────────────┬────────┐
│ id │ name  │ department │ salary │
├────┼───────┼────────────┼────────┤
│ 25 │ Frank │ it         │ 120    │
│ 23 │ Henry │ it         │ 104    │
│ 24 │ Irene │ it         │ 104    │
│ 33 │ Alice │ sales      │ 100    │
│ 31 │ Cindy │ sales      │ 96     │
│ 32 │ Dave  │ sales      │ 96     │
└────┴───────┴────────────┴────────┘

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;

┌────┬───────┬────────────┬────────┐
│ id │ name  │ department │ salary │
├────┼───────┼────────────┼────────┤
│ 33 │ Alice │ sales      │ 100    │
│ 31 │ Cindy │ sales      │ 96     │
│ 32 │ Dave  │ sales      │ 96     │
│ 22 │ Grace │ it         │ 90     │
│ 21 │ Emma  │ it         │ 84     │
└────┴───────┴────────────┴────────┘

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.

Follow @ohmypy on Twitter to keep up with new posts

Blog

LIMIT vs. FETCH in SQL

Anton Zhiyanov

Limit with ties

Relative limit

Offset with fetch

Database support

Join Our Newsletter. No Spam, Only the good stuff.

Related