LIMIT vs TOP vs FETCH FIRST syntax in SQL - Performance Comparison

Time Complexity: LIMIT vs TOP vs FETCH FIRST syntax

O(n log n)

Understanding Time Complexity

When we use LIMIT, TOP, or FETCH FIRST in SQL, we ask the database to return only a small part of the data.

We want to know how this affects the time it takes to get results as the data grows.

Scenario Under Consideration

Analyze the time complexity of these queries that limit output rows.

-- Using LIMIT (common in MySQL, PostgreSQL)
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

-- Using TOP (common in SQL Server)
SELECT TOP 10 * FROM employees ORDER BY salary DESC;

-- Using FETCH FIRST (SQL standard)
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;
    

Each query returns only the top 10 employees by salary from a large table.

Identify Repeating Operations

Look at what the database does repeatedly to answer these queries.

• Primary operation: Scanning and sorting the employee rows by salary.
• How many times: Once over all rows to order them before picking the top 10.

How Execution Grows With Input

The database must look at all rows to find the top 10 salaries.

Input Size (n)	Approx. Operations
10	About 10 rows scanned and sorted
100	About 100 rows scanned and sorted
1000	About 1000 rows scanned and sorted

Pattern observation: As the number of rows grows, the work grows roughly in proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n log n)

This means the time grows a bit faster than the number of rows because sorting takes extra steps.

Common Mistake

[X] Wrong: "Using LIMIT or TOP means the database only looks at 10 rows, so it's always very fast."

[OK] Correct: The database usually must check all rows to find the top 10, so the work depends on the total number of rows, not just 10.

Interview Connect

Understanding how limiting rows affects query time helps you explain database behavior clearly and shows you know how data size impacts performance.

Self-Check

"What if the employees table already has an index on salary? How would that change the time complexity when using LIMIT or TOP?"