SQL FETCH FIRST Clause

SQL FETCH FIRST clause fetches the first given number of rows from the table. 

It is supported in database systems like:

• IBM DB2
• Oracle
• PostgreSQL

Syntax

The syntax to use the FETCH FIRST clause in SQL is:

SELECT columns FROM table WHERE condition FETCH FIRST n ROWS ONLY;

Here,

• n: desired number of rows

SQL FETCH FIRST Clause Example

We will use the same “Employee” table as used in previous examples.

FETCH FIRST clause in SQL Example

In this example, we will fetch the first 3 rows from the table.

Query:

SELECT * FROM Employee FETCH FIRST 3 ROWS ONLY;

Output:

Here, the above query will fetch the first 3 rows only from the table. We can also include some situations using the FETCH FIRST PERCENT and WHERE Clause in the above query.

SQL FETCH FIRST PERCENT Example

In this example, we will fetch first 50% of the data from the table

Query:

SELECT *
FROM Employee
FETCH FIRST (SELECT CEIL(COUNT(*) / 2) FROM Employee) ROWS ONLY;

Output:

Here, the above query fetches the first 50% of the total number of rows (i.e., 2 rows) from the table.

SQL FETCH FIRST with WHERE Clause Example

The “FETCH FIRST” syntax is not supported in MySQL. The correct syntax for limiting the number of rows in MySQL is by using the LIMIT clause.

Query:

SELECT *
FROM Employee
WHERE Salary = 45000
FETCH FIRST 1 ROW ONLY;

Output:

Here, the above query fetches the first 1 row from the table, with the condition that the salary is 45000 (i.e., it returns 1 row only).

SQL TOP, LIMIT, and FETCH FIRST clauses are used to retrieve a specific number of records from a table. These clauses are especially useful in large datasets with thousands of records. Each of these SQL clauses performs a similar operation of limiting the results returned by a query, but they are supported by different database management systems:

• SQL TOP Clause is used in SQL Server and Sybase to limit the number of records returned.
• SQL LIMIT Clause is utilized in MySQL, PostgreSQL, and SQLite.
• SQL FETCH FIRST Clause is part of the SQL standard and is supported by Oracle, DB2, PostgreSQL, and SQL Server (as part of OFFSET-FETCH).

Depending on the database management system (DBMS) being used, you can utilize the respective clause to efficiently manage data retrieval. This article will provide examples and guidance on how to use the SQL TOP, LIMIT, and FETCH FIRST clauses in different SQL environments.