SQL Topics
LIMIT Clause
title: LIMIT Clause
Databases can contain thousands, millions, or even billions of records. In many situations, users do not need every row from a table. Instead, they may only want the first few records, the top results, or a small sample of data.
For example:
- An e-commerce website may display only the first 10 products.
- A social media application may load 20 posts at a time.
- A reporting system may show only the top 5 highest-paid employees.
- A developer may want to preview a few records while testing queries.
Retrieving all rows when only a small subset is needed wastes resources and reduces performance.
To solve this problem, SQL provides the LIMIT Clause.
The LIMIT clause restricts the number of rows returned by a query. It is commonly used in web applications, dashboards, reporting systems, and pagination mechanisms.
What is LIMIT?
The LIMIT clause is used to control the maximum number of rows returned by a query.
Example:
SELECT *
FROM Students
LIMIT 5;Result:
Only the first 5 rows are returned.
Even if the table contains thousands of records, the query stops after retrieving the specified number.
Why is LIMIT Important?
Consider a Products table containing:
500,000 ProductsWithout LIMIT:
SELECT *
FROM Products;The database returns every record.
Problems:
- Slow execution
- Increased memory usage
- Higher network traffic
- Poor user experience
With LIMIT:
SELECT *
FROM Products
LIMIT 10;Only 10 records are returned.
Benefits:
- Faster queries
- Better performance
- Reduced resource consumption
- Improved application responsiveness
Basic LIMIT Syntax
The general syntax is:
SELECT ColumnName
FROM TableName
LIMIT Number;Example:
SELECT *
FROM Students
LIMIT 3;Only three rows are returned.
Understanding the Syntax
Example:
SELECT *
FROM Students
LIMIT 3;SELECT
Retrieves data.
FROM
Specifies the table.
LIMIT
Restricts the number of rows.
3
Maximum rows to return.
The database stops processing after returning three rows.
Creating a Sample Table
Example:
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(100),
Age INT
);Insert data:
INSERT INTO Students VALUES
(1, 'Rahul', 20),
(2, 'Priya', 21),
(3, 'Amit', 19),
(4, 'Neha', 22),
(5, 'Rohan', 20);Current records:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul | 20 |
| 2 | Priya | 21 |
| 3 | Amit | 19 |
| 4 | Neha | 22 |
| 5 | Rohan | 20 |
Retrieving the First Few Rows
Query:
SELECT *
FROM Students
LIMIT 2;Result:
| StudentID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
Only the first two records are returned.
LIMIT with ORDER BY
LIMIT is most useful when combined with ORDER BY.
Example:
SELECT *
FROM Students
ORDER BY Age DESC
LIMIT 3;Process:
Sort Records
ā
Return Top 3 RowsResult:
The three oldest students are returned.
Finding Top Paid Employees
Example:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 5;Result:
The five employees with the highest salaries are displayed.
This is a common business reporting requirement.
LIMIT with WHERE
LIMIT can be combined with filtering.
Example:
SELECT *
FROM Products
WHERE Price > 5000
LIMIT 10;Process:
WHERE ā Filter Records
ā
LIMIT ā Return First 10 RowsOnly matching records are considered.
LIMIT with Multiple Conditions
Example:
SELECT *
FROM Employees
WHERE Department = 'IT'
ORDER BY Salary DESC
LIMIT 3;Result:
The top three highest-paid IT employees are displayed.
Using LIMIT 1
Sometimes only one record is needed.
Example:
SELECT *
FROM Students
LIMIT 1;Result:
Only the first row is returned.
Useful when:
- Testing queries
- Checking table structure
- Retrieving a single record
LIMIT with OFFSET
OFFSET allows skipping rows before returning results.
Syntax:
SELECT *
FROM Students
LIMIT 5 OFFSET 10;Process:
Skip First 10 Rows
ā
Return Next 5 RowsThis is widely used in pagination systems.
Pagination Example
Imagine a website displaying products.
Page 1:
SELECT *
FROM Products
LIMIT 10 OFFSET 0;Page 2:
SELECT *
FROM Products
LIMIT 10 OFFSET 10;Page 3:
SELECT *
FROM Products
LIMIT 10 OFFSET 20;This allows users to browse data page by page.
LIMIT in Different Database Systems
Not all database systems use LIMIT.
MySQL
SELECT *
FROM Students
LIMIT 5;PostgreSQL
SELECT *
FROM Students
LIMIT 5;SQLite
SELECT *
FROM Students
LIMIT 5;SQL Server
Uses TOP instead:
SELECT TOP 5 *
FROM Students;Oracle
Uses FETCH:
SELECT *
FROM Students
FETCH FIRST 5 ROWS ONLY;Real-World Example
Imagine an online shopping platform.
Products table contains:
1,000,000 ProductsHomepage requirement:
Show Latest 20 ProductsQuery:
SELECT *
FROM Products
ORDER BY ProductID DESC
LIMIT 20;Only the newest products are displayed.
This improves performance significantly.
Common Errors
Forgetting ORDER BY
Example:
SELECT *
FROM Students
LIMIT 5;The returned rows may not always be predictable.
Use:
ORDER BY StudentIDwhen order matters.
Negative LIMIT Values
Wrong:
LIMIT -5Most database systems reject negative values.
Assuming LIMIT Sorts Data
Wrong:
SELECT *
FROM Students
LIMIT 5;LIMIT only restricts rows.
Sorting requires:
ORDER BYUsing Large LIMIT Values
Example:
LIMIT 1000000May defeat the purpose of limiting results.
Best Practices
Combine LIMIT with ORDER BY
Good:
SELECT *
FROM Products
ORDER BY Price DESC
LIMIT 10;Use Pagination for Large Datasets
Example:
LIMIT 20 OFFSET 40;Improves user experience.
Retrieve Only Required Columns
Good:
SELECT Name, Age
FROM Students
LIMIT 5;Avoid unnecessary data retrieval.
Use Small Limits During Testing
Example:
LIMIT 5Quickly verifies query results.
Common Interview Questions
What is the purpose of LIMIT?
LIMIT restricts the number of rows returned by a query.
Can LIMIT be used with ORDER BY?
Yes.
This is one of the most common use cases.
What is OFFSET?
OFFSET skips a specified number of rows before returning results.
Does LIMIT sort data?
No.
Sorting is performed using ORDER BY.
Summary
The LIMIT clause is an essential SQL feature used to restrict the number of rows returned by a query. It improves performance, supports pagination, and helps retrieve only the data that is actually needed.
In this lesson, you learned:
- What LIMIT is
- Why LIMIT is important
- Basic syntax
- LIMIT with ORDER BY
- LIMIT with WHERE
- LIMIT with OFFSET
- Pagination
- Database-specific alternatives
- Common mistakes
- Best practices
Understanding LIMIT is important because modern applications frequently display data in small, manageable sections rather than loading entire datasets at once.
Next Step
Continue to the next lesson:
TOP Clause ā
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for LIMIT Clause.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, filtering, limit
Related SQL Topics