SQL LIMIT & FETCH FIRST

💻 Introduction

When working with large datasets, you often don’t want all rows at once. SQL provides row limiting clauses such as LIMIT, FETCH FIRST, and TOP to control how many rows are returned.

Different databases use different syntax:

• MySQL / PostgreSQL: LIMIT
• Oracle / DB2 / PostgreSQL (SQL:2008 standard): FETCH FIRST
• SQL Server: TOP

1️⃣ MySQL & PostgreSQL – LIMIT

Use LIMIT to restrict the number of rows.

-- Return first 5 employees
SELECT * FROM Employees
LIMIT 5;

LIMIT with OFFSET

-- Skip first 5 rows, return next 5
SELECT * FROM Employees
LIMIT 5 OFFSET 5;

2️⃣ Oracle / PostgreSQL / DB2 – FETCH FIRST

FETCH FIRST is part of the SQL standard for row limiting.

-- Return first 10 employees
SELECT * FROM Employees
FETCH FIRST 10 ROWS ONLY;

FETCH with OFFSET

-- Skip first 10 rows, return next 5
SELECT * FROM Employees
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

3️⃣ SQL Server – TOP

SQL Server uses TOP to limit rows.

-- Return top 5 employees
SELECT TOP 5 * FROM Employees;

TOP with PERCENT

-- Return top 10% of employees
SELECT TOP 10 PERCENT * FROM Employees;

✅ Key Takeaways

• LIMIT → Used in MySQL & PostgreSQL
• FETCH FIRST → SQL standard (Oracle, PostgreSQL, DB2)
• TOP → Used in SQL Server
• OFFSET → Skips rows (used for pagination)
• Row limiting is essential for performance and pagination

📚 Continue Learning