MySQL LIMIT & OFFSET with Examples

What is the LIMIT keyword?

The limit keyword is used to limit the number of rows returned in a query result.

It can be used in conjunction with the SELECT, UPDATE OR DELETE commands LIMIT keyword syntax

The syntax for the LIMIT keyword is as follows

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT  N;

HERE

“SELECT {fieldname(s) | *} FROM tableName(s)” is the SELECT statement containing the fields that we would like to return in our query.
“[WHERE condition]” is optional but when supplied, can be used to specify a filter on the result set.
“LIMIT N” is the keyword and N is any number starting from 0, putting 0 as the limit does not return any records in the query. Putting a number say 5 will return five records. If the records in the specified table are less than N, then all the records from the queried table are returned in the result set.

Let’s look at an example –

SELECT *  FROM members LIMIT 2;




membership_ number
full_ names
gender
date_of _birth
date_of _registration
physical_ address
postal_ address
contact_ number
email
credit_ card_ number




1
Janet Jones
Female
21-07-1980
NULL
First Street Plot No 4
Private Bag
0759 253 542
janetjones@yagoo.cm
NULL


2
Janet Smith Jones
Female
23-06-1980
NULL
Melrose 123
NULL
NULL
jj@fstreet.com
NULL

membership_ number	full_ names	gender	date_of _birth	date_of _registration	physical_ address	postal_ address	contact_ number	email	credit_ card_ number
1	Janet Jones	Female	21-07-1980	NULL	First Street Plot No 4	Private Bag	0759 253 542	janetjones@yagoo.cm	NULL
2	Janet Smith Jones	Female	23-06-1980	NULL	Melrose 123	NULL	NULL	jj@fstreet.com	NULL

As you can see from the above screenshot, only two members have been returned.

Getting a list of ten (10) members only from the database

Let’s suppose that we want to get a list of the first 10 registered members from the Myflix database. We would use the following script to achieve that.

SELECT *  FROM members LIMIT 10;

Executing the above script gives us the results shown below




membership_ number
full_ names
gender
date_of _birth
date_of _registration
physical_ address
postal_ address
contact_ number
email
credit_ card_ number




1
Janet Jones
Female
21-07-1980
NULL
First Street Plot No 4
Private Bag
0759 253 542
janetjones@yagoo.cm
NULL


2
Janet Smith Jones
Female
23-06-1980
NULL
Melrose 123
NULL
NULL
jj@fstreet.com
NULL


3
Robert Phil
Male
12-07-1989
NULL
3rd Street 34
NULL
12345
rm@tstreet.com
NULL


4
Gloria Williams
Female
14-02-1984
NULL
2nd Street 23
NULL
NULL
NULL
NULL


5
Leonard Hofstadter
Male
NULL
NULL
Woodcrest
NULL
845738767
NULL
NULL


6
Sheldon Cooper
Male
NULL
NULL
Woodcrest
NULL
976736763
NULL
NULL


7
Rajesh Koothrappali
Male
NULL
NULL
Woodcrest
NULL
938867763
NULL
NULL


8
Leslie Winkle
Male
14-02-1984
NULL
Woodcrest
NULL
987636553
NULL
NULL


9
Howard Wolowitz
Male
24-08-1981
NULL
SouthPark
P.O. Box 4563
987786553
lwolowitz[at]email.me
NULL

membership_ number	full_ names	gender	date_of _birth	date_of _registration	physical_ address	postal_ address	contact_ number	email	credit_ card_ number
1	Janet Jones	Female	21-07-1980	NULL	First Street Plot No 4	Private Bag	0759 253 542	janetjones@yagoo.cm	NULL
2	Janet Smith Jones	Female	23-06-1980	NULL	Melrose 123	NULL	NULL	jj@fstreet.com	NULL
3	Robert Phil	Male	12-07-1989	NULL	3rd Street 34	NULL	12345	rm@tstreet.com	NULL
4	Gloria Williams	Female	14-02-1984	NULL	2nd Street 23	NULL	NULL	NULL	NULL
5	Leonard Hofstadter	Male	NULL	NULL	Woodcrest	NULL	845738767	NULL	NULL
6	Sheldon Cooper	Male	NULL	NULL	Woodcrest	NULL	976736763	NULL	NULL
7	Rajesh Koothrappali	Male	NULL	NULL	Woodcrest	NULL	938867763	NULL	NULL
8	Leslie Winkle	Male	14-02-1984	NULL	Woodcrest	NULL	987636553	NULL	NULL
9	Howard Wolowitz	Male	24-08-1981	NULL	SouthPark	P.O. Box 4563	987786553	lwolowitz[at]email.me	NULL

Note only 9 members have been returned in our query since N in the LIMIT clause is greater than the number of total records in our table.

Re-writing the above script as follows

SELECT *  FROM members LIMIT 9;

Only returns 9 rows in our query result set.

Using the OFF SET in the LIMIT query

The OFF SET value is also most often used together with the LIMIT keyword. The OFF SET value allows us to specify which row to start from retrieving data

Let’s suppose that we want to get a limited number of members starting from the middle of the rows, we can use the LIMIT keyword together with the offset value to achieve that. The script shown below gets data starting the second row and limits the results to 2.

SELECT * FROM `members` LIMIT 1, 2;

Executing the above script in MySQL workbench against the myflixdb gives the following results.

membership_ number	full_ names	gender	date_of _birth	date_of _registration	physical_ address	postal_ address	contact_ number	email	credit_ card_ number
2	Janet Smith Jones	Female	23-06-1980	NULL	Melrose 123	NULL	NULL	jj@fstreet.com	NULL
3	Robert Phil	Male	12-07-1989	NULL	3rd Street 34	NULL	12345	rm@tstreet.com	NULL

Note that here OFFSET = 1 Hence row#2 is returned & Limit = 2, Hence only 2 records are returned

When should we use the LIMIT keyword?

Let’s suppose that we are developing the application that runs on top of myflixdb. Our system designer have asked us to limit the number of records displayed on a page to say 20 records per page to counter slow load times. How do we go about implementing the system that meets such user requirements? The LIMIT keyword comes in handy in such situations. We would be able to limit the results returned from a query to 20 records only per page.

Summary

The LIMIT keyword of is used to limit the number of rows returned from a result set.
The LIMIT number can be any number from zero (0) going upwards. When zero (0) is specified as the limit, no rows are returned from the result set.
The OFF SET value allows us to specify which row to start from retrieving data
It can be used in conjunction with the SELECT, UPDATE OR DELETE commands LIMIT keyword syntax