# 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.