Let’s take a common situation where you are developing an application that provides the option to page through data sets 50 records at a time. If the above statement is to be used, either the entire record set will need to be cached by the application or the query will be run once per page, returning all the matching records each time. This is a very inefficient situation, particularly for very large record sets. Consequently, most contemporary database engines support for limiting the size of the returned record set. Below is a summary of how this is achieved in different databases.
|
Database
|
SQL Syntax
|
|
DB2
|
SELECT * FROM table FETCH FIRST 50 ROWS ONLY
|
|
Informix
|
SELECT FIRST 50 * FROM table
|
|
Microsoft SQL
|
SELECT TOP 50 * FROM table
|
|
MySQL
|
SELECT * FROM table LIMIT 50
|
|
PostgreSQL
|
SELECT * FROM table LIMIT 50
|
|
Oracle 8i
|
SELECT * FROM (SELECT * FROM table)
WHERE ROWNUM <= 50 |