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:

1. 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.

2. 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.

3. Keyword OFFSET can be specified in conjunction with LIMIT to specify from which row we want the data to be retrieved from. It's equivalent to the comma version in Practice #1 above but you need to swap the position of the two numbers. See Practice #3.

4. If ORDER BY clause is used in the SELECT statement, the LIMIT clause must be placed after the ORDER BY clause. See Practice #1.

5. LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. See Practice #4.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

Practice #1: Use two numeric arguments in LIMIT clause

/*
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:

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:

Practice #3: Using LIMIT with OFFSET

OFFSET indicates which row we want the data to be retrieved from.

The first query below uses OFFSET with LIMIT. It retrieves 10 records starting from the 6th row in table products.

LIMIT 10 OFFSET 5 is equivalent to LIMIT 5,10 in the second query. You need to swap the two numbers if you use the shorter version without keyword OFFSET.

SELECT ProductID, ProductName
FROM products
ORDER BY ProductID
LIMIT 10 OFFSET 5;

-- Equivalent query
SELECT ProductID, ProductName
FROM products
ORDER BY ProductID
LIMIT 5,10;

Query result set - 10 rows returned:

Practice #4: 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:

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Perform Arithmetic Operations

5. Use WHERE Clause to Conditionally Select Rows

6. Sorting Data

7. Using Comparison Operators, Part I

8. Using Comparison Operators, Part II

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page