How to Use LIMIT Clause in SQL

How to Use LIMIT Clause in SQL

7 mins read2.8K Views Comment
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Jan 9, 2024 14:02 IST

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.

2022_07_MicrosoftTeams-image-223.jpg

Table of Content

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_name
FROM table_name
WHERE conditions
ORDER BY expression [ASC | DESC]
LIMIT number;
Copy code

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
Employee Dataset

Must Check: SQL SELECT

Must Read: Types of Keys in Database

Example 1: Find the record of the first three employees.

Query


 
SELECT *
FROM Employee
LIMIT 3;
Copy code

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, CTC
FROM Employee
WHERE Education = ‘UG’
LIMIT 3;
Copy code

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, CTC
FROM Employee
WHERE Education = ‘UG’
ORDER BY CTC DESC
LIMIT 3;
Copy code

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 Employee
ORDER BY Employee ID
LIMIT 3 OFFSET 3;
Copy code

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.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

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