{"id":197237,"date":"2025-07-14T05:30:00","date_gmt":"2025-07-14T04:30:00","guid":{"rendered":"https:\/\/liora.io\/en\/?p=197237"},"modified":"2026-02-17T10:27:13","modified_gmt":"2026-02-17T09:27:13","slug":"sql-limit-and-offset","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/sql-limit-and-offset","title":{"rendered":"SQL LIMIT and OFFSET: Controlling Query Results"},"content":{"rendered":"\n

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.<\/strong><\/p>\n\n\n\n

To prevent unnecessary load and display only what you need, SQL provides tools to limit your query results \u2014 namely, the LIMIT and OFFSET clauses.<\/strong><\/p>\n\n\n\n

Why Use LIMIT?<\/strong><\/p>\n\n\n\n

Here are common use cases where LIMIT is extremely useful:<\/p>\n\n\n\n

\u2705 Situation 1: Data Preview<\/h3>\n\n\n\n

When you are just getting started and want to take a quick look at a table\u2019s content, there is no need to load the entire dataset. Use LIMIT to display the first few rows for a data preview.<\/p>\n\n\n\n

\u2705 Situation 2: Top-N Analysis with ORDER BY<\/h3>\n\n\n\n

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

\u2705 Situation 3: Data Sampling<\/h3>\n\n\n\n

If you are conducting exploratory analysis or testing a model, you may want to sample a subset of rows.<\/p>\n\n\n\n

\u27a1\ufe0f This gives you a manageable chunk of data to work with, especially from large tables.<\/p>\n\n\n\n

\u2705 Situation 4: Data Pagination<\/h3>\n\n\n\n

When working with dashboards, APIs, or web applications, you often need to paginate results \u2014 e.g., show 10 results per page.<\/p>\n\n\n\n

\"Un<\/figure>\n\n\n\n
\n
Get to grips with SQL<\/a><\/div>\n<\/div>\n\n\n\n

Example Table<\/h2>\n\n\n\n

Let\u2019s say you are working with this customer table:<\/p>\n\n\n\n

\n \n \n \n \n \n \n \n
customer_id<\/th>\n first_name<\/th>\n last_name<\/th>\n email<\/th>\n email_verified<\/th>\n phone_number<\/th>\n <\/tr>\n <\/thead>\n
101<\/td>\n John<\/td>\n Doe<\/td>\n john@email.com<\/td>\n Y<\/td>\n 0912…<\/td>\n <\/tr>\n\n
102<\/td>\n Jane<\/td>\n Smith<\/td>\n NULL<\/td>\n NULL<\/td>\n 0933…<\/td>\n <\/tr>\n\n
103<\/td>\n Mike<\/td>\n Lee<\/td>\n ”<\/td>\n N<\/td>\n  <\/td>\n <\/tr>\n <\/tbody>\n <\/table>\n<\/div>\n\n\n\n
SELECT * \nFROM customers\nORDER BY customer_id\nLIMIT 2;<\/pre>\n\n\n\n
\u27a1\ufe0f This would return rows 1 and 2.<\/strong><\/p>\n\n\n\n
SELECT * \nFROM customers\nORDER BY customer_id\nLIMIT 2 OFFSET 1;<\/pre>\n\n\n\n
\u27a1\ufe0f This would return rows 2 and 3, effectively paginating the results.<\/strong><\/p>\n\n\n\n
\"Un<\/figure>\n\n\n\n
\n
Become an SQL expert<\/a><\/div>\n<\/div>\n\n\n\n
How is LIMIT Different from TOP and FETCH FIRST?<\/h2>\n\n\n\n
Different SQL dialects use different syntax for limiting rows:<\/p>\n\n\n\n
\n  \n    \n      \n        \n      \n        \n        \n        
Syntax<\/th>\n        SQL Flavor<\/th>\n        Example<\/th>\n      <\/tr>\n    <\/thead>\n    
LIMIT \/ OFFSET<\/strong><\/td>\n        MySQL, PostgreSQL, SQLite<\/td>\n        \n          SELECT *
\n          FROM table
\n          LIMIT 10 OFFSET 20;\n        <\/td>\n      <\/tr>\n\n      
TOP<\/strong><\/td>\n        SQL Server<\/td>\n        \n          SELECT TOP 10 *
\n          FROM table;\n        <\/td>\n      <\/tr>\n\n      
FETCH FIRST<\/strong><\/td>\n        ANSI SQL \/ Oracle \/ newer SQL Server versions<\/td>\n        \n          SELECT *
\n          FROM table
\n          ORDER BY id
\n          FETCH FIRST 10 ROWS ONLY;\n        <\/td>\n      <\/tr>\n    <\/tbody>\n  <\/table>\n<\/div>\n\n\n\n
<\/div>\n\n\n\n
\u26a0\ufe0f Key Differences:<\/p>\n\n\n\n