{"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-08-09T19:54:55","modified_gmt":"2026-08-09T18:54:55","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

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

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

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

\u2705 Situation 1: Data Preview<\/h3>\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

\u2705 Situation 2: Top-N Analysis with ORDER BY<\/h3>\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

\u2705 Situation 3: Data Sampling<\/h3>\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

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

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

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

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

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

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

\n\n\n\n\n\n\n
customer_id<\/th>\nfirst_name<\/th>\nlast_name<\/th>\nemail<\/th>\nemail_verified<\/th>\nphone_number<\/th>\n<\/tr>\n<\/thead>\n
101<\/td>\nJohn<\/td>\nDoe<\/td>\njohn@email.com<\/td>\nY<\/td>\n0912…<\/td>\n<\/tr>\n
102<\/td>\nJane<\/td>\nSmith<\/td>\nNULL<\/td>\nNULL<\/td>\n0933…<\/td>\n<\/tr>\n
103<\/td>\nMike<\/td>\nLee<\/td>\n”<\/td>\nN<\/td>\n\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/figure>\n\n\n
SELECT *\u00a0\nFROM customers\nORDER BY customer_id\nLIMIT 2;<\/code><\/pre>\n\n\n
\u27a1\ufe0f This would return rows 1 and 2.<\/strong><\/p>\n\n\n
SELECT *\u00a0\nFROM customers\nORDER BY customer_id\nLIMIT 2 OFFSET 1;<\/code><\/pre>\n\n\n
\u27a1\ufe0f This would return rows 2 and 3, effectively paginating the results.<\/strong><\/p>\n\n\n
\"Un<\/figure>\n\n\n\n
How is LIMIT Different from TOP and FETCH FIRST?<\/h2>\n\n\n
Different SQL dialects use different syntax for limiting rows:<\/p>\n\n\n
\n\n\n\n\n\n\n
Syntax<\/th>\nSQL Flavor<\/th>\nExample<\/th>\n<\/tr>\n<\/thead>\n
LIMIT \/ OFFSET<\/strong><\/td>\nMySQL, PostgreSQL, SQLite<\/td>\n\n          SELECT *\n          FROM table\n          LIMIT 10 OFFSET 20;\n        <\/td>\n<\/tr>\n
TOP<\/strong><\/td>\nSQL Server<\/td>\n\n          SELECT TOP 10 *\n          FROM table;\n        <\/td>\n<\/tr>\n
FETCH FIRST<\/strong><\/td>\nANSI 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><\/figure>\n\n\n
\u26a0\ufe0f Key Differences:<\/p>\n\n\n