I recently stumbled on the Fetch clause in PostgreSQL which is functionally the same as the Limit clause, and this short article tries to explain these two clauses and make a distinction between them.

Limit Clause

This SQL clause is used when you want to limit the number of rows returned by your query. In an SQL query the limit clause comes after the order by clause which is used to order result in either ascending or descending order.

The syntax of the limit clause is:

The “number of row(s)” is an integer value that is used to specify the number of rows to return from the query result. It takes an optional Offset clause that is used to specify the number of records to skip before returning “number of rows” result. If no offset is specified, no records will be skipped before limiting the result.

Sample usage in a select query:

Fetch Clause

The fetch clause functions similarly to the limit clause. It is used to limit the number of rows returned by an sql query. Like the limit clause, it comes after the order by clause.

The syntax for the fetch clause is ;

The fetch clause can also take an optional Offset clause and if no offset is specified, no record will be skipped before fetching the result.

Sample usage in a select query:

Differences between them

While fetch and limit have the same functionality, the fetch clause is an SQL standard while limit is not. Fetch was included in the SQL standard in 2008 which makes it the go to option if you want your query to be portable across several RDBMS. It is also worth mentioning that the limit clause is also quite popular as some RDBMS adopts it.

Thank you for reading.

Data Analyst | Business Intelligence Analyst | Tableau Expert.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store