LIMIT / TOP Clause

The LIMIT (or TOP in T-SQL / SQL Server) clause specifies the maximum number of records a SQL query will return. It is essential for optimizing query performance, preventing memory overflow when inspecting large tables, and implementing pagination in web applications.

1. Why Use LIMIT / TOP?

In enterprise data warehouses with billions of rows, running SELECT * FROM sales; without row restrictions can lock database tables, consume gigabytes of network bandwidth, and crash client applications.

Key use cases for limiting rows:

• Fast Data Sampling: Inspect schema and sample data instantaneously.
• Top N Ranking: Retrieve top 5 highest earners or top 10 best-selling products (when combined with ORDER BY).
• Pagination: Load large result sets in pages (e.g., Page 1: rows 1-20, Page 2: rows 21-40).

2. Standard SQL Syntax: LIMIT

In PostgreSQL, MySQL, SQLite, Snowflake, DuckDB, and Spark SQL, the LIMIT clause is placed at the very end of the query.

SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Explanation:

1. ORDER BY salary DESC sorts all employees from highest salary to lowest.
2. LIMIT 5 restricts the output set to the top 5 records.

3. SQL Server & Sybase Syntax: TOP

Microsoft SQL Server uses the TOP keyword directly after SELECT.

SELECT TOP 5 employee_id, first_name, salary
FROM employees
ORDER BY salary DESC;

You can also specify a percentage of rows in SQL Server:

SELECT TOP 10 PERCENT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC;

4. Pagination with LIMIT & OFFSET

To implement web page navigation (Page 1, Page 2, Page 3), combine LIMIT with OFFSET. OFFSET specifies how many rows to skip before starting to return rows.

-- Page 1: Retrieve rows 1 to 10
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Page 2: Skip first 10 rows, retrieve next 10 rows (rows 11-20)
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

5. SQL Standard 2008 Syntax: FETCH FIRST

Oracle 12c+, DB2, and modern SQL standards support OFFSET ... FETCH FIRST n ROWS ONLY:

SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 0 ROWS
FETCH FIRST 5 ROWS ONLY;

6. Summary Comparison Matrix

| Database Engine | Top Rows Syntax | Pagination Syntax | | :--- | :--- | :--- | | PostgreSQL / MySQL / SQLite | LIMIT 10 | LIMIT 10 OFFSET 20 | | Spark SQL / DuckDB | LIMIT 10 | LIMIT 10 OFFSET 20 | | SQL Server (T-SQL) | TOP (10) | OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY | | Oracle (12c+) | FETCH FIRST 10 ROWS ONLY | OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY | | Snowflake | LIMIT 10 | LIMIT 10 OFFSET 20 |