Today, I am going to explain how to use the SQL LIMIT clause to limit output values. We all know RDBMS systems are one of the popular ways to store, protect, access, and analyze data in a structured way. We can store enormous sizes of data and then further can access them with the help of SQL language to get our desired output to analyze it. SQL stands for Structured Query Language which is used to perform activities like creating, modifying, and accessing data from the databases hosted on RDBMS systems like SQL Server or MySQL, etc. This article will explain one of the SQL query statements LIMIT which we use to limit the number of records returned in the output result.
Sometimes we need to filter the output result with a limited number of rows, or you can say we just want to return a certain number of rows. The SQL LIMIT clause is designed to fulfill this requirement. We can limit the number of records or display only the same number of records which is there mentioned in the LIMIT clause. This SQL statement cannot be used in all RDBMS systems like SQL Server, Oracle, etc. but all these RDBMS systems have their query statement to limit the rows returned in the output result. You can use the TOP statement in SQL Server whereas Oracle has another clause that is FETCH FIRST n ROWS ONLY. The SQL LIMIT clause can be used in MySQL, PostgreSQL, and other RDBMS systems.
The syntax to use the SQL LIMIT clause is given below.
|
1 2 3 4 5 |
SELECT ColumName FROM TableName LIMIT number, [Offset value] ; |
Here,
- ColumnName is the name of the columns which you want to return in your output
- TableName is the name of the table from which you want to return the result set in your output
- Offset is the number from which the result will display. By default, the value is 0
- The number is the number of records you want to display in the output
Next, I will demonstrate a few use cases of the SQL LIMIT clause on a MySQL database in the below section. Let me tell you the source table details on which I will demonstrate these use cases.
I have a table named tblEmployee in our MySQL database TESTDB. I have inserted 10 records in this table to show you the results of the below use cases. You can see the data from this table in the below image.
I executed below SQL statement to display all records from this table.
|
1 2 3 |
SELECT * FROM tblEmployee |
Have a look at its output in the below image.
Use Cases
This section will help you understand the SQL LIMIT clause with the help of its use cases, you will also learn how to use this clause with other SQL statements like WHERE, ORDER BY, OFFSET, etc. Let’s start with its basic use case in the below section where I will limit the output to return only a specified number of records.
Basic use case
You can see in the last image which I have returned all the records of the specified table. I have created this table for this demonstration which is why I have inserted only 10 rows. Suppose you have a similar table with thousands or millions of records then it would be costly to return all rows. It’s always advisable to return only records that are needed for your requirement to reduce the workload and improve performance.
Now, again come back to our source table where we have 10 rows as given in the last image. Let’s assume, we want to display only the first 5 rows of this table. We can use the SQL LIMIT clause in the above SQL statement to return only the first 5 rows. Let me show you this example with the help of the below use case. Here, I have used the same SQL statement which I used to display all records of the table in the above image. I just added the SQL LIMIT clause along with the number of rows or records which we want to return i.e., 5 rows so I added LIMIT 5 in the same SQL statement. Let’s run it and see the difference.
|
1 2 3 4 5 |
SELECT * FROM tblEmployee LIMIT 5; |
I have executed the above SQL statement in the below image, and you can see now that the output has returned only the first 5 rows of this table. You can validate its data by comparing it with the last image value.
Use the SQL LIMIT clause with WHERE clause
Let’s understand how this SQL LIMIT clause works with the WHERE clause. Suppose you want to return the first 5 records of this table based on some condition specified under the WHERE clause. We can use the same SQL statement as above for this use case as well, we will just add a WHERE clause to add a conditional filter to see the output.
Here, I want to return the first 5 employees’ details whose last name is Singh. I have specified this condition in the WHERE clause as it is showing in the below SQL statement.
|
1 2 3 4 5 6 |
SELECT * FROM tblEmployee WHERE Employee_last_name = ‘singh’ LIMIT 5; |
Let’s run it and see the difference. Here, we have 5 records only but this time these 5 records are not matching with the records returned in the last use case where we had not specified the WHERE clause. The below result has returned the top 5 employees whose last name is Singh. You can validate the output with the first image of this article.
Use the SQL LIMIT clause with ORDER BY statement
This section will show you how to use SQL LIMIT clause with ORDER BY statement and with WHERE clause & ORDER BY statement together. We will consider the same SQL query from the above use cases and modify it to include this use case. Here, I have added the ORDER BY clause on the Employee_last_name column and then specified LIMIT 5 to display the first 5 rows in the specified order.
|
1 2 3 4 5 6 |
SELECT * FROM tblEmployee ORDER Employee_last_name LIMIT 5; |
I have executed the above query and got the below output. Here, you can see the output is ordered by the employee’s last name column and has returned only 5 records which are specified with SQL LIMIT statement.
We can also use the WHERE clause, ORDER BY clause along with SQL LIMIT clause together. Look at the below example where I have filtered the result by employee’s last name from the table and then order it by employee’s first name and then finally limit the result to return only 5 records. Go ahead and run it to get the output.
|
1 2 3 4 5 6 7 |
SELECT * FROM tblEmployee WHERE Employee_last_name = ‘singh’ ORDER BY Employee_first_name LIMIT 5; |
The output of the above query is shown in the below image. Here we can see only those employees whose last name is ‘Singh’, also returned result is ordered by their first name and the total number of returned records is only 5 which is specified in the SQL LIMIT clause.
Use the SQL LIMIT clause with OFFSET statement
The SQL LIMIT clause can also be used with the OFFSET statement to return results from the specified number of rows. We know, if you mention LIMIT 8 it means, the result will display the top 8 rows of the table in the output but what if you want to display the result from row no 2 to row no 8 only or a portion of the table. Let’s understand it with the help of an example. Suppose you have 10 rows in the table, and you want to fetch rows starting from row no 8 to row no 10 only i.e. only 2 rows then we can use the OFFSET statement along with the LIMIT clause to meet this business requirement. Let’s understand it more with the help of the below use case.
|
1 2 3 4 5 |
SELECT * FROM tblEmployee LIMIT 2 OFFSET 8; |
If you look at the last line of the above query then I have specified OFFSET 8, which means the output will return from row no 9, and have specified LIMIT 2 which means it will display the next 2 rows after row no 8 which will be row no 9 and row no 10. Have a look at its output in the below image to analyze it.
You can use various other clauses like WHERE and ORDER BY along with LIMIT and OFFSET statements. Let me show you one use case for this. Here, I have used the same query which I used in one of the use cases described above for the WHERE and ORDER BY clauses and added both LIMIT and OFFSET statements as the last line of this query as you can see below.
|
1 2 3 4 5 6 7 |
SELECT * FROM tbleEmployee WHERE Employee_last_name=’singh’ ORDER BY Employee_first_name LIMIT 5 OFFSET 2; |
The above query will return 5 employee details starting from row no 3 as specified in the OFFSET statement. The output will return from the list of records that will satisfy the WHERE and ORDER BY conditions. Now, have a look at the below image and observe its output.
Here, you might be thinking why employee id 2 is showing in the list whereas we have mentioned OFFSET as 2 so ideally it should come from row no 3. To understand this, you first need to run the above query without having an OFFSET statement. Once you will analyze that output, you will get to know that employee id 2 is 3rd record in the output.
I have explained this in the below image. You can see the first set of queries in which I have not included the OFFSET statement. As we have specified LIMIT 5 so the output has returned 5 rows based on the WHERE clause of having Singh as the last name and then reorders this output based on their first name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--Get output without OFFSET SELECT * FROM tbleEmployee WHERE Employee_last_name=’singh’ ORDER BY Employee_first_name LIMIT 5; --Specify OFFSET to display only a portion of rows SELECT * FROM tbleEmployee WHERE Employee_last_name=’singh’ ORDER BY Employee_first_name LIMIT 5 OFFSET 2; |
You can see employee id 2 is showing as the 3rd row in the output of the first query so when you will run the same statement with OFFSET 2 then you will get the last 3 records of this output which is showing in the second query or last query which I executed in the above image.
Conclusion
We discussed the SQL LIMIT clause and its use cases in this article. This SQL statement is used to limit the number of records in the query output on MySQL databases. We cannot use this syntax in all RDBMS systems because there are different syntaxes used by distinct RDBMS systems to limit rows in the output result set. SQL Server uses TOP statements whereas Oracle has their syntax which I described above in this article.
Thank you for reading this article and I would encourage you all to write your feedback in the comment section so that we can improve in a better way.
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023
- PostgreSQL vs MySQL: Understanding their differences - March 13, 2023