SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

When working with large databases, it can be useful to retrieve only a limited number of records from a table. This is where the SQL TOP, LIMIT, FETCH FIRST or ROWNUM clauses come into play. These clauses allow you to specify the maximum number of records to be retrieved from a table.

Here is a brief overview of each of these clauses:

SQL TOP Clause

The SQL TOP clause is used in Microsoft SQL Server and Sybase SQL to limit the number of records retrieved from a table. The syntax is as follows:

1SELECT TOP n columns
2FROM table_name;

where n is the maximum number of records to be retrieved and columns are the columns you want to retrieve.

For example, the following statement retrieves the first 5 records from the "employees" table:

1SELECT TOP 5 *
2FROM employees;

SQL LIMIT Clause

The SQL LIMIT clause is used in MySQL and PostgreSQL to limit the number of records retrieved from a table. The syntax is as follows:

1SELECT columns
2FROM table_name
3LIMIT n;

where n is the maximum number of records to be retrieved and columns are the columns you want to retrieve.

For example, the following statement retrieves the first 10 records from the "employees" table:

1SELECT *
2FROM employees
3LIMIT 10;

SQL FETCH FIRST Clause

The SQL FETCH FIRST clause is used in IBM DB2 to limit the number of records retrieved from a table. The syntax is as follows:

1SELECT columns
2FROM table_name
3FETCH FIRST n ROWS ONLY;

where n is the maximum number of records to be retrieved and columns are the columns you want to retrieve.

For example, the following statement retrieves the first 20 records from the "employees" table:

1SELECT *
2FROM employees
3FETCH FIRST 20 ROWS ONLY;

SQL ROWNUM Clause

The SQL ROWNUM clause is used in Oracle to limit the number of records retrieved from a table. The syntax is as follows:

1SELECT columns
2FROM (SELECT columns, ROWNUM r
3      FROM (SELECT columns
4            FROM table_name
5           )
6      WHERE ROWNUM <= n
7     )
8WHERE r > m;

where n is the maximum number of records to be retrieved, m is the starting record number, and columns are the columns you want to retrieve.

For example, the following statement retrieves the records from the "employees" table, starting from the 10th record and limited to 5 records:

1SELECT *
2FROM (SELECT *, ROWNUM r
3      FROM (SELECT *
4            FROM employees
5           )
6      WHERE ROWNUM <= 15
7     )
8WHERE r > 10;