How to Use LIMIT Clause in SQL
As a database administrator or developer, you know how crucial it is to manage data loads and optimize query performance. One of the essential tools in your toolkit is the SQL LIMIT clause, which allows you to control the number of records returned from a SQL query. This feature is particularly useful in large databases where only a subset of records is needed for analysis or display purposes. This article dives deeper into the SQL LIMIT clause and explore how it can help you improve query performance and streamline your data management process.
Table of Content
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
What is the LIMIT clause?
When you are working on a dataset that contains thousands or millions of records and returns a large number of tuples satisfying any query, then you need to restrict the result (or you want to filter the result) to get the exact match.
To restrict the result, we can use the LIMIT clause in SQL.
The LIMIT clause in the SQL is used to restrict the number of records.
Or
Simply put, it sets an upper limit on the number of tuples returned for any given query.
The limit expression must be a non-negative integer.
Note:
The LIMIT clause is an efficient way to avoid long-running queries as it stops processing and returns the result as soon as the requirement is fulfilled. This reduces the processing time.
But what will happen if the query contains the ORDER BY clause or any aggregate functions? Then, in that case, the LIMIT clause is the last to be evaluated.
Must Read: Introduction to SQL
Must Read: What is the difference between SQL and MySQL?
LIMIT Syntax:
SELECT column_nameFROM table_nameWHERE conditionsORDER BY expression [ASC | DESC]LIMIT number;
Now, let’s have some examples for better understanding:
For the examples we will use Employee Datasets that contain Employee ID, Name, Gender, Department, Education, Month of Joining and and their salary (CTC) of 10 employees.
| Employee ID | Name | Gender | Department | Education | Month of Joining | CTC(in Lacs) |
| 1001 | Ajay | M | Engineering | Doctoral | January | 25 |
| 1002 | Babloo | M | Engineering | UG | February | 23 |
| 1003 | Chhavi | F | HR | PG | March | 15 |
| 1004 | Dheeraj | M | HR | UG | January | 12 |
| 1005 | Evina | F | Marketing | UG | March | 16 |
| 1006 | Garima | M | Sales | UG | December | 10 |
| 1007 | Fredy | F | Sales | PG | March | 10 |
| 1008 | Hans | M | Admin | PG | November | 8 |
| 1009 | Ivanka | F | Admin | Intermediate | April | 7 |
| 1010 | Jai | M | Peon | December | 4 |
Must Check: SQL SELECT
Must Read: Types of Keys in Database
Example 1: Find the record of the first three employees.
Query
SELECT *FROM EmployeeLIMIT 3;
Note: In the above query, we are using * in place of giving any particular column name. * is used when we want the result from all the columns.
Output
| Employee ID | Name | Gender | Department | Education | Month of Joining | CTC(in Lacs) |
| 1001 | Ajay | M | Engineering | Doctoral | January | 25 |
| 1002 | Babloo | M | Engineering | UG | February | 23 |
| 1003 | Chhavi | F | HR | PG | March | 15 |
The above table represents the record of the first three employees of the company.
Must Read: SQL Joins
Must Read: Introduction to Normalization
LIMIT with WHERE clause
Example 2: Find the record of the first three employees (Employee ID, Name, Department, Education, and CTC) who are undergraduates.
Query
SELECT Employee ID, Name, Department, Education, CTCFROM EmployeeWHERE Education = ‘UG’LIMIT 3;
Output
| Employee ID | Name | Department | Education | CTC(in Lacs) |
| 1002 | Babloo | Engineering | UG | 23 |
| 1004 | Dheeraj | HR | UG | 12 |
| 1005 | Evina | Marketing | UG | 16 |
LIMIT with WHERE and ORDER BY clause
Example 3: Find the record of the top three highest-paid employees (Employee ID, Name, Department, Education, and CTC) who are just undergraduates.
Query
SELECT Employee ID, Name, Department, Education, CTCFROM EmployeeWHERE Education = ‘UG’ORDER BY CTC DESCLIMIT 3;
Output
| Employee ID | Name | Department | Education | CTC(in Lacs) |
| 1002 | Babloo | Engineering | UG | 23 |
| 1005 | Evina | Marketing | UG | 16 |
| 1004 | Dheeraj | HR | UG | 12 |
Now, in both examples, we are getting the same record but with a slight difference. The records in example 3 are sorted according to CTC (in descending order).
Now moving forward, if you want to skip the first two, three, or more entries and want the record (or entries) after that.
Here, OFFSET comes into action with LIMIT.
Must Read: Difference between SQL and NoSQL
Must Read: Window Function in SQL
LIMIT m OFFSET n
LIMIT m OFFSET n simply skip the first n record and then display the next m records.
Note: OFFSET can only be used with the ORDER BY clause.
Example 4: We will use the above employee table. We want the record of employee codes 1004, 1005, and 1006.
Query
SELECT *FROM EmployeeORDER BY Employee IDLIMIT 3 OFFSET 3;
Output
| Employee ID | Name | Gender | Department | Education | Month of Joining | CTC(in Lacs) |
| 1004 | Dheeraj | M | HR | UG | January | 12 |
| 1005 | Evina | F | Marketing | UG | March | 16 |
| 1006 | Garima | M | Sales | UG | December | 10 |
Note: The LIMIT ALL clause returns all the rows (entries) in the table, i.e. LIMIT ALL implies no limit.
Restriction on LIMIT clause
It is not valid in
- the definition of a view
- subqueries that contain table expression in FORM clause
- nested SELECT statement
- where embedded SELECT statements are used as expressions
Must Check: SQL Online Course and Certifications
Difference Between LIMIT and OFFSET
| Parameter | LIMIT Clause | OFFSET Clause |
|---|---|---|
| Functionality | Specifies the maximum number of rows to return in a query result. | Specifies the number of rows to skip before starting to return rows from the query. |
| Usage | Used to limit the result set to a certain number of rows. | Used in conjunction with LIMIT to skip a specified number of rows. |
| Syntax | Typically used as LIMIT [number]. For example, LIMIT 10 it returns the first 10 rows. | Typically used as OFFSET [number]. For example, OFFSET 5 skips the first 5 rows. |
| Purpose | Mainly used for data paging and controlling the size of result sets. | Used for pagination, especially to define the starting point of the result set to be returned. |
| Combination with Other Clauses | Often used in combination with ORDER BY for ordered data slicing. | Generally used together with LIMIT to define both the start and size of the data slice. |
| Default Behavior | If not specified, all rows are returned. | If not specified, the default starting point is the first row (equivalent to OFFSET 0). |
| Application | Commonly used in scenarios like report generation, data sampling, or when working with large datasets to avoid performance issues. | Essential for implementing pagination in web applications, APIs, and database queries to display results across multiple pages. |
Conclusion
The SQL LIMIT clause is a powerful tool for managing and retrieving data efficiently in databases. It allows you to restrict the number of records returned by a SQL query, which is particularly useful when dealing with large datasets where performance and speed are critical. Whether you're optimizing the user experience in an application by paginating results, conducting data analysis or just ensuring faster query responses, the LIMIT clause is simple yet highly effective. It is also versatile and can be applied across various SQL databases. This article aims to provide you with a comprehensive understanding of the LIMIT clause, improving your skills and confidence in managing and querying databases. Hope you will like the article.
Keep Learning!!
Keep Sharing!!
FAQs on SQL LIMIT Clause
What is the SQL LIMIT Clause?
The SQL LIMIT clause is used to specify the maximum number of rows that should be returned from a query. It's beneficial in large databases for managing data loads and improving query performance.
How is the LIMIT Clause Used with OFFSET?
The OFFSET statement can be used alongside the LIMIT clause to skip a specified number of rows before returning rows from the query. For instance, LIMIT 5 OFFSET 10 would skip the first 10 rows and then return the next 5 rows.
Can LIMIT and OFFSET be Used with WHERE and ORDER BY Clauses?
Yes, a SQL query can combine the LIMIT and OFFSET clauses with WHERE and ORDER BY clauses. This allows for more precise control over the returned rows, especially when filtering and sorting data.
How Do Different SQL Databases Implement the LIMIT Clause?
While databases like MySQL, PostgreSQL, and SQLite use the LIMIT clause, SQL Server uses the TOP clause, and Oracle uses FETCH FIRST...ROWS ONLY. Each of these achieves a similar result but with slightly different syntax.
How do you fetch a percentage of rows using the LIMIT Clause?
In Oracle, for example, you can fetch a top percentage of rows using the FETCH FIRST...PERCENT ROWS ONLY clause. This allows you to retrieve a portion of rows based on their ordered position.
How Can LIMIT be Used for Pagination?
The LIMIT clause is ideal for implementing pagination in applications. By adjusting the OFFSET and LIMIT values, you can control which page of results is shown, such as displaying 10 results per page.
Is There a Difference Between FETCH FIRST and LIMIT?
FETCH FIRST is similar to LIMIT but is used in SQL dialects like Oracle. It is generally easier to use correctly and more in line with other SQL dialects than Oracle's ROWNUM approach.
How to Use SQL Limit with Subqueries?
LIMIT can be used within subqueries to retrieve a limited set of intermediate results. This is useful for managing large datasets and improving the performance of complex queries.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
