MySQL - LIMIT Clause

The MySQL LIMIT clause is used to fetch specified number 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 LIMIT clause 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:

• 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 LIMIT clause

The OFFSET value is most often used with the LIMIT clause. 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:

• 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