MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • 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:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
  • 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:

    EmpIDNameCityAgeSalary
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000

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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • 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:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000
  • 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:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000

❮ MySQL Keywords