MySQL LIMIT Operator


Introduction

The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

The following illustrates the LIMIT clause syntax with two arguments:

1
2
3
SELECT select_list
FROM table_name
LIMIT [offset,] row_count;

In this syntax:

  • The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
  • The row_count specifies the maximum number of rows to return.

When you use the LIMIT clause with one argument, MySQL will use this argument to determine the maximum number of rows to return from the first row of the result set. Therefore, the following two clauses are equivalent.

1
2
LIMIT row_count;
LIMIT 0 , row_count;

Examples

For example, in a web database application, where we want to find the rows that match a condition but only want to show the user the first 10 rows in a web page. Here’s an example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT track_name FROM track LIMIT 10;
+----------------------------+
| track_name                 |
+----------------------------+
| Do You Love Me?            |
| Nobody's Baby Now          |
| Loverman                   |
| Jangling Jack              |
| Red Right Hand             |
| I Let Love In              |
| Thirsty Dog                |
| Ain't Gonna Rain Anymore   |
| Lay Me Low                 |
| Do You Love Me? (Part Two) |
+----------------------------+

The LIMIT clause in this example restricts the output to the first 10 rows, saving the cost of buffering, communicating, and displaying the remaining 143 tracks.

Here is another example. Suppose we want five rows, but we want the first one displayed to be the sixth row of the answer set. We do this by starting from after the fifth answer:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT track_name FROM track LIMIT 5,5;
+----------------------------+
| track_name                 |
+----------------------------+
| I Let Love In              |
| Thirsty Dog                |
| Ain't Gonna Rain Anymore   |
| Lay Me Low                 |
| Do You Love Me? (Part Two) |
+----------------------------+




Related Posts

MySQL INNER JOIN Clause

The INNER JOIN keyword selects records that have matching values...

MySQL Aliases

Aliases are nicknames. They give us a shorthand way of...

MySQL OR Operator

The OR operator combines two Boolean expressions and returns true...

MySQL LIMIT Operator

The LIMIT clause is used in the SELECT statement to...

MySQL LIKE Operator

The LIKE operator is used in a WHERE clause to...

MySQL IS NULL Operator

To test whether a value is NULL or not, we...