Difference between Top and Limit Keyword in SQL

Knowing your SQL - TOP, LIMIT and OFFSET

Compatibility concerns

Each SQL dialect comes with its own nuances. You need to consider database compatibility. For an instance, SQL Server doesn't provide offset capability out of the box with TOP and it needs you to play around with subqueries or additional windows functions like ROW_NUMBER().

Implementing pagination with OFFSET

To implement functionalities like pagination, you can pair LIMIT with OFFSET in MySQL:

-- Pagination - Because who has time to see all the rows at once
SELECT * FROM products LIMIT 10 OFFSET 30; 


This jumps over the first 30 records and fetches the next 10.

Getting top percentage results

SQL Server adds another twist here by allowing us to use TOP with a percentage:

-- The boss wants the TOP performers, no matter if they are 10 or 20
SELECT TOP 10 PERCENT * FROM sales; 


This queries for the top 10% of soul-crushing sales records, not available with MySQL's LIMIT.

Tuning up SQL queries - Pro tips

Oracle DBAs' guide to LIMIT

For all those Oracle database fanatics, they have provided with their own version of LIMIT mechanism - rownum:

-- "Rows before bros", says Oracle 
SELECT * FROM employees WHERE rownum <= 10; 


Use this when declaring SQL dominance with Oracle databases.

The additional SQL family - ROWNUM and OFFSET-FETCH

SQLite and SQL Server's OFFSET-FETCH

Some SQL products, like SQLite and SQL Server (2012 onwards), have also adopted more universal approach with the OFFSET-FETCH support:

-- SQL Server trying to LIMIT its bad OFFSET
SELECT * FROM employees 
ORDER BY employee_id 
OFFSET 0 ROWS   -- Skip nothing, we're not quitters
FETCH NEXT 10 ROWS ONLY; -- But FETCH only 10 because too much of everything is bad


Always remember, the cardinal SQL rule - know your database's native features.

Querying for fun and profit

Also, bear in mind, the use of LIMIT, TOP and their likes can significantly improve the query performance. It can control the memory consumption and reduce I/O cycles.