Use LIMIT Clause to Constrain the Number of Rows Returned in SQL SELECT Statement for MySQL

Use LIMIT Clause to Constrain the Number of Rows Returned

If a table contains one million rows and you only want to retrieve data from row 1 to row 100, you can use the LIMIT clause. The LIMIT clause is used to constrain the number of rows returned by the SELECT statement.

Read Prerequisites for this tutorial and practices if you haven't done so.

Consider the following facts when using LIMIT clause in a SELECT statement:

LIMIT takes one or two numeric arguments, which must both be non-negative integer constants.

If the LIMIT uses two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. See Practice #1.
If the LIMIT uses only one argument, the value specifies the number of rows to return from the beginning of the result set. See Practice #2.
If ORDER BY clause is used in the SELECT statement, the LIMIT clause must be placed after the ORDER BY clause. See Practice #1.
LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. See Practice #3.

Practice #1: Use two numeric arguments in LIMIT clause

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
The first argument specifies the offset of 
the first row (starting from 0) to return. The 
second specifies the maximum number of rows to 
return.
 
This query retrieves rows 6 to 15
*/
SELECT ProductID, ProductName
FROM products
ORDER BY ProductID
LIMIT 5,10;

Query result set - 10 rows returned:
Using LIMIT Clause with two arguments

Practice #2: Use one argument in LIMIT clause

/*
When the first argument is omitted, the only value 
in LIMIT clause specifies the number of rows to return 
from the beginning of the result set.
 
Query 1: This query returns rows 1 to 15
*/
SELECT ProductID, ProductName
FROM products 
ORDER BY ProductID
LIMIT 15;
 
/*
Query 2: This query returns the same rows as Query 1.
It shows that the first row starts with offset of 0.
 
In other words, LIMIT 15 is equivalent to LIMIT 0,15
*/
SELECT ProductID, ProductName
FROM products 
ORDER BY ProductID
LIMIT 0,15;

Query result set - 15 rows returned:
Using LIMIT Clause with one arguments

Practice #3: Using LIMIT 0 to validate a query rather than execute it

/*
LIMIT 0 quickly returns an empty set. This can 
be useful for checking the validity of a query.
*/
SELECT ProductID, ProductName
FROM products 
ORDER BY ProductID
LIMIT 0;

LIMIT 0 is very useful when you need to validate a complex query. Sometimes when you expect a query to return a large number of rows but you are not sure if the SQL syntax is correct, you can use LIMIT 0 to validate the query. This reduces server load.

Query result set - 0 rows returned:
Use LIMIT 0 to validate a query

Next tutorial: Using Comparison Operators, Part I
Back to Section Index: Single Table SELECT Statement
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search