- Aggregate functions
- ALTER TABLE statement
- AVERAGE function
- BETWEEN operator
- CASE expression
- CAST() function
- COALESCE() function
- Comment
- Common table expression
- CONCAT() function
- Constraints
- CONTAINS
- CONVERT function
- COUNT() function
- CREATE TABLE statement
- CROSS JOIN
- Cursor
- Data types
- Date functions
- DATEADD() function
- DATEDIFF() function
- DELETE statement
- DROP TABLE statement
- EXISTS operator
- FORMAT() function
- GROUP BY statement
- HAVING clause
- IF statement
- Index
- Injection
- INNER JOIN
- INSERT INTO statement
- IS NOT NULL condition
- IS NULL condition
- Joins
- LAG function
- LEFT JOIN
- LENGTH() function
- LIKE operator
- LIMIT clause
- MERGE statement
- Normalization
- Not equal
- Operators
- ORDER BY clause
- Partition
- Pivot table
- Regex
- REPLACE function
- ROUND function
- SELECT DISTINCT clause
- SELECT statement
- Set operators
- Stored procedure
- String functions
- Subquery
- Substring
- Temporary table
- Transaction
- Trigger
- TRUNCATE TABLE
- UPDATE statement
- Views
- WHERE clause
- Window functions
SQL
SQL LIMIT Clause: Syntax, Usage, and Examples
The SQL LIMIT
clause allows you to control how many rows are returned in a result set. When working with large tables, retrieving all available rows may not be efficient or necessary. This is where the LIMIT SQL
clause becomes useful. It provides a way to cap the output, reduce load times, and support features like pagination or previews in user interfaces.
Supported by systems such as MySQL, PostgreSQL, and SQLite, the clause is typically placed at the end of a SELECT
query to restrict the number of records returned. Although not every database supports LIMIT
natively, equivalent approaches exist in systems like SQL Server and Oracle.
Understanding LIMIT in SQL
The LIMIT
clause defines a maximum number of rows that the database engine should return when executing a query. For example, if a query would normally return 1,000 records, adding LIMIT 10
ensures only the first 10 are retrieved. This can dramatically improve performance when only a subset of the data is needed for display or analysis.
Using LIMIT
is particularly important in web applications and dashboards where queries must run quickly and results need to be digestible. In combination with ORDER BY
, it allows developers to control not only how many results are shown but also which results are prioritized.
Basic Syntax of the SQL LIMIT Clause
The general syntax is straightforward:
SELECT column1, column2
FROM table_name
LIMIT number_of_rows;
This statement instructs the SQL engine to return a specific number of rows from the specified table. For instance, LIMIT 5
at the end of a query will restrict output to five rows, regardless of how many rows exist in the database.
In some systems like MySQL, you can also use the format LIMIT offset, row_count
to skip a number of rows and then return a specific amount. This is particularly helpful for implementing pagination.
Simple Example of SQL LIMIT
Imagine a table named Books
with hundreds of entries. To preview only the first five records, the following query would suffice:
SELECT * FROM Books
LIMIT 5;
This query helps you quickly inspect data, test your query’s correctness, or build a UI that only displays the latest additions.
Pagination with SQL LIMIT and OFFSET
While LIMIT
on its own is useful, pairing it with OFFSET
unlocks pagination. OFFSET
tells the database how many rows to skip before it begins returning data. The combination is ideal when presenting data across multiple pages.
The syntax usually follows this pattern:
SELECT column1, column2
FROM table_name
LIMIT row_count OFFSET skip_count;
For instance, if you want to retrieve records 11 through 20 from a table, you would skip the first 10 rows and then return the next 10:
SELECT * FROM Customers
LIMIT 10 OFFSET 10;
Alternatively, in MySQL and SQLite, you can use LIMIT 10, 10
, where the first number is the offset and the second is the row count.
Why Use LIMIT in SQL Queries
The LIMIT SQL
clause is indispensable in many real-world scenarios. For example, in an e-commerce application, you might only want to show 20 products at a time. Using LIMIT 20
makes this possible. When debugging a query, viewing the entire result set may be overwhelming, so using LIMIT 5
or LIMIT 10
lets you focus on a few rows.
LIMIT is also frequently used in analytical queries that need to display top results, like the five highest-selling products or the most recent transactions. These use cases often rely on combining LIMIT
with ORDER BY
to define what “top” or “recent” means.
Using LIMIT with ORDER BY
Although LIMIT
can be used on its own, it’s often paired with ORDER BY
to determine the priority of returned rows. Without ORDER BY
, the rows returned by a LIMIT
query may appear arbitrary, depending on the database’s storage or indexing behavior.
To get the most recent entries from a table, you could use:
SELECT * FROM Orders
ORDER BY order_date DESC
LIMIT 5;
This retrieves the latest five orders, sorted in descending order by date. Similarly, you can use ascending order to retrieve the earliest entries. This pattern is extremely common in dashboards, admin panels, and financial reports.
LIMIT SQL in Different Database Systems
Not all SQL dialects use the LIMIT
clause. In systems that do, such as MySQL, PostgreSQL, and SQLite, the syntax is consistent. In SQL Server, however, the TOP
keyword is used instead:
SELECT TOP 5 * FROM Employees;
Alternatively, SQL Server 2012 and later supports OFFSET
and FETCH NEXT
for pagination:
SELECT * FROM Employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Oracle uses ROWNUM
in older versions, and FETCH FIRST
in newer releases:
SELECT * FROM Employees
FETCH FIRST 5 ROWS ONLY;
These syntax variations achieve the same result as LIMIT
, though their implementation and performance characteristics may differ slightly.
LIMIT SQL for Aggregated Queries
The LIMIT
clause can also be useful when dealing with aggregation. If you want to find the top departments by headcount, you might write:
SELECT department, COUNT(*) AS total
FROM Employees
GROUP BY department
ORDER BY total DESC
LIMIT 3;
This returns only the top three departments with the highest number of employees. The aggregation occurs first, and the limit is applied to the final results. This approach is widely used in reporting systems and leaderboards.
Using LIMIT in Subqueries
Subqueries often benefit from the LIMIT
clause, especially when a main query depends on only one or a few rows from a subresult. For instance, to find the employee with the highest salary, you could use:
SELECT name, salary
FROM Employees
WHERE salary = (
SELECT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1
);
This isolates the top salary first, then uses it to find the corresponding employee in the outer query. It’s a clean and readable way to find top performers or most recent records in combination with other logic.
Managing Performance with LIMIT
Using LIMIT
can improve query performance by preventing large result sets from overwhelming the application or client. However, there are some caveats. When used with large offsets, performance may degrade. Skipping thousands of rows forces the database to scan them before reaching the desired output.
For example, OFFSET 10000 LIMIT 10
may lead to noticeable lag as the system must still traverse the initial 10,000 rows. For applications dealing with massive data volumes, keyset pagination—where results are fetched based on the last item from the previous page—is often more efficient.
Additionally, LIMIT doesn’t reduce the underlying query complexity. If you join multiple large tables or apply filters with high computational cost, the query must process those operations before limiting the result count.
LIMIT Clause with Joins
LIMIT
works seamlessly after JOIN
operations, applying to the final result after tables are combined. Suppose you want to get only 10 rows from a join between Orders
and Customers
:
SELECT o.id, c.name
FROM Orders o
JOIN Customers c ON o.customer_id = c.id
ORDER BY o.order_date DESC
LIMIT 10;
The join is evaluated first, then ordered by date, and finally trimmed to 10 rows. This structure is useful in reporting recent purchases, support tickets, or user actions.
Best Practices
When using LIMIT
, always include ORDER BY
if the result order matters. This avoids non-deterministic outputs. Use small limits when querying large datasets during development. Combine LIMIT
with indexing for optimal performance. Avoid large offsets in production, and instead rely on indexed IDs or timestamps for efficient pagination.
Summary
The SQL LIMIT
clause is a foundational tool for controlling the number of rows returned by a query. It improves performance, enables pagination, and supports top-N analysis in data-heavy environments. In MySQL, PostgreSQL, and SQLite, the clause is simple and powerful, while other systems offer equivalents like TOP
, ROWNUM
, or FETCH FIRST
.
By understanding the different ways to apply LIMIT SQL
—including with OFFSET
, ORDER BY
, aggregation, subqueries, and joins—you can write cleaner, faster, and more efficient queries that scale well in production environments.
Sign up or download Mimo from the App Store or Google Play to enhance your programming skills and prepare for a career in tech.