When we work with a huge amount of data there are cases when we need to restrict the number of rows that should be returned as part of the query.
In order to help us with the same, SQL provides us with a feature called SQL Limit. This clause has to be the last one in the query otherwise you will get an error.
SQL Limit Clause
MySQL, PostgreSQL and many more databases support the usage of SQL Limit clause. The SQL query is executed and finally, the number of results specified by the LIMIT clause is returned. If the result set size is smaller than the rows specified by the LIMIT clause, then all the result set is returned.
Let’s try to understand the usage of SQL Limit clause.
SQL Limit Example
Syntax:
|
1 2 3 4 5 |
SELECT column_name(s) FROM table_name LIMIT number |
We will consider the following table for understanding SQL Limit in a better way.
Student
| StudentId | StudentName | State | Country |
|---|---|---|---|
| 1 | Henry | Wales | UK |
| 2 | Rohit | Delhi | India |
| 3 | Steve | London | UK |
Below is the query to create the table and insert some sample data. Note that I am using PostgreSQL, so you might have to make some changes to run it in other databases.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE `student` ( `StudentId` INT NOT NULL, `StudentName` VARCHAR(45) NULL, `State` VARCHAR(45) NULL, `Country` VARCHAR(45) NULL, PRIMARY KEY (`StudentId`), UNIQUE INDEX `StudentId_UNIQUE` (`StudentId` ASC) VISIBLE); Insert into Student(StudentId,StudentName,State,Country) VALUES (1, 'Henry','Wales','UK'), (2, 'Rohit','Delhi','India'), (3, 'Steve','London','UK'); |
Now we will try to limit the display result to just 2 rows.
|
1 2 3 |
SELECT * FROM student limit 2; |
Output:
| StudentId | StudentName | State | Country |
|---|---|---|---|
| 1 | Henry | Wales | UK |
| 2 | Rohit | Delhi | India |
SQL Limit
There are times when we need to limit the number of rows based on some condition. We will try to understand the scenario better in the next section.
SQL Limit with Where Clause
SQL Limit can also be used along with WHERE clause.
SQL Limit with Where Clause Syntax:
|
1 2 3 4 |
SELECT column_name(s) FROM table_name WHERE condition LIMIT number |
We will consider the same student table for this case as well.
Let us try to get one entry from the table with the country as UK.
|
1 2 3 |
SELECT * FROM student where country= "UK" limit 1; |
Output:
| StudentId | StudentName | State | Country |
|---|---|---|---|
| 1 | Henry | Wales | UK |
SQL Limit with Where clause
MySQL Limit
MySQL database also supports LIMIT clause. Above examples are from PostgreSQL database. Let’s look at some examples from MySQL database.
Here is the script to create a sample table with some data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE `Customer` ( `CustomerId` int(11) unsigned NOT NULL AUTO_INCREMENT, `CustomerName` varchar(20) DEFAULT NULL, `CutomerAge` int(11) DEFAULT NULL, `CustomerGender` varchar(2) DEFAULT NULL, PRIMARY KEY (`CustomerId`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO `Customer` (`CustomerId`, `CustomerName`, `CutomerAge`, `CustomerGender`) VALUES (1, 'John', 31, 'M'), (2, 'Amit', 25, 'M'), (3, 'Annie', 35, 'F'), (4, 'Tom', 38, 'M'); |
Let’s run a simple query to select only 3 rows from the Customer table.
|
1 2 3 |
select * from Customer limit 3; |
We can use Order By clause with LIMIT too. In this case, the query will be executed and finally, only the number of rows specified by the limit clause will be returned.
|
1 2 3 |
select * from Customer order by CustomerName limit 2; |
Finally, let’s look at an example of using the LIMIT clause with WHERE condition.
|
1 2 3 |
select * from Customer where CustomerGender="M" limit 2; |
MySQL Limit Offset
Most of the times, the limit clause is used to process bulk records with pagination. So it’s useful when it’s used with offset. The syntax of the limit clause with offset is:
|
1 2 3 4 5 |
SELECT column_name(s) FROM table_name LIMIT offset_number, number |
The offset number specifies the rows to skip before the specified number of rows to be returned in the result. Let’s understand this with a simple example.
|
1 2 3 |
Select * from Customer limit 1, 2; |
Our Customer table has 4 rows. So the first row is skipped and the next two rows are returned in the result. Let’s look at one more example for SQL limit offset query.
|
1 2 3 |
select * from Customer limit 2, 1; |
Here only the third row from the Customer table will be returned.
Conclusion
SQL LIMIT clause helps us in achieving pagination in our application. It’s very helpful if we have to process huge result-set data by limiting the result set size.