MySQL SELECT LIMIT Keyword
The MySQL SELECT LIMIT keyword is used to fetch specified number or percentage of records from a table. This is useful when the table contains thousands of records and returning a large dataset can impact performance.
Syntax
The syntax for using SELECT LIMIT keyword in MySQL is given below:
SELECT column1, column2, ... FROM table_name WHERE condition(s) LIMIT number;
Example:
Consider a database containing a table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
-
To fetch top 3 records from the Employee table, the query is:
SELECT * FROM Employee LIMIT 3;
This will produce the result as shown below:
EmpID Name City Age Salary 1 John London 25 3000 2 Marry New York 24 2750 3 Jo Paris 27 2800 -
To fetch top 3 records where the Age of the employee is greater than 25, the query will be:
SELECT * FROM Employee WHERE Age > 25 LIMIT 3;
This will produce the result as shown below:
EmpID Name City Age Salary 3 Jo Paris 27 2800 4 Kim Amsterdam 30 3100 5 Ramesh New Delhi 28 3000
Using OFFSET with SELECT LIMIT query
The OFFSET value is most often used with the SELECT LIMIT query. The OFFSET value allows the user to specify which row to start from to retrieve the data.
Syntax
The syntax for using OFFSET keyword in MySQL is given below:
/* method 1 */ SELECT column1, column2, ... FROM table_name WHERE condition(s) LIMIT fetch_row_count OFFSET offset_row_count; /* method 2 */ SELECT column1, column2, ... FROM table_name WHERE condition(s) LIMIT offset_row_count, fetch_row_count;
Example:
Consider a database containing a table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
-
To fetch top 2-5 records from the Employee table, the query is:
SELECT * FROM Employee LIMIT 4 OFFSET 1;
This will produce the result as shown below:
EmpID Name City Age Salary 2 Marry New York 24 2750 3 Jo Paris 27 2800 4 Kim Amsterdam 30 3100 5 Ramesh New Delhi 28 3000 -
The same result can be achieved by using the below query also.
SELECT * FROM Employee LIMIT 1, 4;
This will produce the result as shown below:
EmpID Name City Age Salary 2 Marry New York 24 2750 3 Jo Paris 27 2800 4 Kim Amsterdam 30 3100 5 Ramesh New Delhi 28 3000
❮ MySQL Keywords