🚀 Think you’ve got what it takes for a career in Data? Find out in just one minute!

SQL LIMIT and OFFSET: Controlling Query Results

-
2
 m de lecture
-
Illustration d'un homme utilisant une interface de requête SQL, mettant en avant les instructions 'LIMIT' et 'OFFSET' pour contrôler les résultats de la requête.

In most contexts, a table can contain thousands or even millions of rows. If you run a query that returns all records, it can overload your database, slow down performance, and make debugging harder.

To prevent unnecessary load and display only what you need, SQL provides tools to limit your query results — namely, the LIMIT and OFFSET clauses.

📌 Why Use LIMIT?

Here are common use cases where LIMIT is extremely useful:

✅ Situation 1: Data Preview

When you are just getting started and want to take a quick look at a table’s content, there is no need to load the entire dataset. Use LIMIT to display the first few rows for a data preview.

✅ Situation 2: Top-N Analysis with ORDER BY

If you want to know the top 10 bestsellers, most recent records, or highest-rated products, you can combine ORDER BY and LIMIT.

✅ Situation 3: Data Sampling

If you are conducting exploratory analysis or testing a model, you may want to sample a subset of rows.

➡️ This gives you a manageable chunk of data to work with, especially from large tables.

✅ Situation 4: Data Pagination

When working with dashboards, APIs, or web applications, you often need to paginate results — e.g., show 10 results per page. 

Un utilisateur consultant un tableau de clients sur un écran d'ordinateur, illustrant l'utilisation de SQL LIMIT et OFFSET pour contrôler les résultats de requêtes.

🛠 Example Table

Let’s say you are working with this customer table:

customer_id first_name last_name email email_verified phone_number
101 John Doe john@email.com Y 0912...
102 Jane Smith NULL NULL 0933...
103 Mike Lee '' N
SELECT * 
FROM customers
ORDER BY customer_id
LIMIT 2;

➡️ This would return rows 1 and 2.

SELECT * 
FROM customers
ORDER BY customer_id
LIMIT 2 OFFSET 1;

➡️ This would return rows 2 and 3, effectively paginating the results.

Un analyste de données examinant une table dans une interface de requête SQL, illustrant l'utilisation des commandes LIMIT et OFFSET pour le contrôle des résultats de requête.

🧠 How is LIMIT Different from TOP and FETCH FIRST?

Different SQL dialects use different syntax for limiting rows:

Syntax SQL Flavor Example
LIMIT / OFFSET MySQL, PostgreSQL, SQLite SELECT *
FROM table
LIMIT 10 OFFSET 20;
TOP SQL Server SELECT TOP 10 *
FROM table;
FETCH FIRST ANSI SQL / Oracle / newer SQL Server versions SELECT *
FROM table
ORDER BY id
FETCH FIRST 10 ROWS ONLY;

⚠️ Key Differences:

  • LIMIT is widely used in MySQL, PostgreSQL, and SQLite.
  • TOP works only in SQL Server (and can’t be used with OFFSET).
  • FETCH FIRST … ROWS ONLY is ANSI-standard, more verbose, but also more portable in newer systems (like Oracle or DB2).

✅ Conclusion

Whether you are previewing data, performing top-N analysis, sampling rows, or implementing pagination, knowing how to control the number of results returned by your SQL queries is essential. The LIMIT clause — often used with OFFSET — gives you a simple yet powerful way to manage result sets efficiently.

Different SQL dialects may use LIMIT, TOP, or FETCH FIRST, but the goal remains the same: optimize your queries, reduce load on your database, and retrieve only what you need.

By mastering these tools, you not only improve performance but also gain greater flexibility in exploring and presenting your data.

Facebook
Twitter
LinkedIn

DataScientest News

Sign up for our Newsletter to receive our guides, tutorials, events, and the latest news directly in your inbox.

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter

DataNews

Get monthly insider insights from experts directly in your mailbox