DEV Community

Cover image for LIMIT vs. FETCH in SQL
Anton Zhiyanov
Anton Zhiyanov

Posted on • Originally published at antonz.org

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;
Enter fullscreen mode Exit fullscreen mode
┌────┬───────┬────────────┬────────┐
│ id │ name  │ department │ salary │
├────┼───────┼────────────┼────────┤
│ 25 │ Frank │ it         │ 120    │
│ 23 │ Henry │ it         │ 104    │
│ 24 │ Irene │ it         │ 104    │
│ 33 │ Alice │ sales      │ 100    │
│ 31 │ Cindy │ sales      │ 96     │
└────┴───────┴────────────┴────────┘
Enter fullscreen mode Exit fullscreen mode

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

select * from employees
order by salary desc
fetch first 5 rows only;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
┌────┬───────┬────────────┬────────┐
│ 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     │
└────┴───────┴────────────┴────────┘
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
┌────┬───────┬────────────┬────────┐
│ id │ name  │ department │ salary │
├────┼───────┼────────────┼────────┤
│ 25 │ Frank │ it         │ 120    │
│ 23 │ Henry │ it         │ 104    │
└────┴───────┴────────────┴────────┘
Enter fullscreen mode Exit fullscreen mode

(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;
Enter fullscreen mode Exit fullscreen mode
┌────┬───────┬────────────┬────────┐
│ id │ name  │ department │ salary │
├────┼───────┼────────────┼────────┤
│ 33 │ Alice │ sales      │ 100    │
│ 31 │ Cindy │ sales      │ 96     │
│ 32 │ Dave  │ sales      │ 96     │
│ 22 │ Grace │ it         │ 90     │
│ 21 │ Emma  │ it         │ 84     │
└────┴───────┴────────────┴────────┘
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

Top comments (1)

Collapse
 
rozhnev profile image
Slava Rozhnev

Very good explanation