FETCH FIRST Clause in Oracle

FETCH FIRST Clause in Oracle with Examples

In this article, I am going to discuss FETCH FIRST Clause in Oracle with Examples. Please read our previous article where we discussed ROLLUP and CUBE Clauses in Oracle with Examples.

FETCH FIRST Clause in Oracle

The FETCH FIRST Clause in Oracle is used to specify the number of records or rows to return. This FETCH FIRST Clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Syntax to use FETCH FIRST Clause in Oracle:

Following is the syntax to use FETCH FIRST Clause in Oracle.

FETCH FIRST Clause in Oracle with Examples

Example to understand FETCH FIRST Clause in Oracle:

We are going to use the following Employee table to understand the FETCH FIRST Clause in Oracle with Examples.

Example to understand FETCH FIRST Clause in Oracle

Please execute the below SQL query to drop the existing Employee table if any and create a new Employee table with the required data.

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Comm INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 3500, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1002, 'Smith', 'HR', 45000, 'Male', 4500, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 5000, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', NULL, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', NULL, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', NULL, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', NULL, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1008, 'Sambit', 'IT', 55000, 'Female', 5500, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1009, 'Pranaya', 'IT', 57000, 'Female', 5700, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1010, 'Hina', 'HR', 75000, 'Male', 7500, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1011, 'Warner', 'Finance', 55000, 'Female', NULL, 'London');
Using FETCH FIRST Clause in Oracle with Examples

In the below example, we are using the FETCH FIRST 3 ROWS ONLY clause to select the first 3 records from the Employee table.

SELECT * FROM Employee FETCH FIRST 3 ROWS ONLY;

When you run the above query, you will get the following output.

Using FETCH FIRST Clause in Oracle with Examples

Example: Fetch the first 3 records from the Employee table where Gender is Male. 

SELECT * FROM Employee WHERE Gender = ‘Male’ FETCH FIRST 3 ROWS ONLY;

When you run the above query, you will get the following output.

FETCH FIRST Clause in Oracle

FETCH FIRST PERCENT Clause in Oracle

The following SQL statement selects the first 50% of the records from the Employee table.

SELECT * FROM Employee FETCH FIRST 50 PERCENT ROWS ONLY;

When you run the above query, you will get the following output.

FETCH FIRST PERCENT Clause in Oracle

In the next article, I am going to discuss Functions in Oracle with Examples. Here, in this article, I try to explain FETCH FIRST Clause in Oracle with Examples and I hope you enjoy this FETCH FIRST Clause in Oracle with Examples article.

Leave a Reply

Your email address will not be published.