Understanding the LIMIT Clause in SQL

Some of the major relational database management systems (RDBMSs) have a LIMIT clause that enables us to reduce the number of rows returned by a query.

The way it works is that we provide the number of rows we want to be returned by the query. We can also provide an offset to specify which row to start the count from.

Example

Suppose we have a table with the following data:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId;

Result:

+-----------+---------------------------------+
| ProductId | ProductName                     |
+-----------+---------------------------------+
|         1 | Left handed screwdriver         |
|         2 | Right handed screwdriver        |
|         3 | Long Weight (blue)              |
|         4 | Long Weight (green)             |
|         5 | Sledge Hammer                   |
|         6 | Chainsaw                        |
|         7 | Straw Dog Box                   |
|         8 | Bottomless Coffee Mugs (4 Pack) |
|         9 | Hammock                         |
|        10 | Tea Pot                         |
+-----------+---------------------------------+
10 rows in set (0.11 sec)

This query returned ten rows.

We can use the LIMIT clause to reduce the number of rows returned:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
LIMIT 3;

Result:

+-----------+--------------------------+
| ProductId | ProductName              |
+-----------+--------------------------+
|         1 | Left handed screwdriver  |
|         2 | Right handed screwdriver |
|         3 | Long Weight (blue)       |
+-----------+--------------------------+
3 rows in set (0.00 sec)

Using an Offset

Most (if not all) RDBMSs that provide a LIMIT clause allow us to skip rows at the beginning of the output. We do this by specifying an offset.

This is probably explained best by an example:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
LIMIT 3 OFFSET 2;

Result:

+-----------+---------------------+
| ProductId | ProductName         |
+-----------+---------------------+
|         3 | Long Weight (blue)  |
|         4 | Long Weight (green) |
|         5 | Sledge Hammer       |
+-----------+---------------------+
3 rows in set (0.00 sec)

In this case I specified an offset of 2. This meant that the LIMIT conditions were applied after skipping two rows.

So in this example we still got three rows. We just skipped two rows before counting the three rows. So we didn’t get products 1 through 3 like in the previous example. We got products 3 through 5 instead.

Some RDBMSs (such as MySQL, MariaDB, and SQLite) accept a shortened form for including offset. In such cases we can rewrite the above query as follows:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
LIMIT 2, 3;

Result:

+-----------+---------------------+
| ProductId | ProductName         |
+-----------+---------------------+
|         3 | Long Weight (blue)  |
|         4 | Long Weight (green) |
|         5 | Sledge Hammer       |
+-----------+---------------------+
3 rows in set (0.06 sec)

So in this case the offset is the first value, and the row count limit is the second.

Alternatives to the `LIMIT` Clause

The LIMIT clause is not supported in all RDBMSs. However, some RDBMSs provide an alternative method for limiting the rows returned by a query, and for setting an offset.

In SQL Server, we can include TOP(n) in our SELECT list to return the top n rows. SQL Server also supports a FETCH and OFFSET clause that can be used to limit the rows returned with an offset. We can use these two clauses to implement pagination functionality.

Oracle Database also implements a FETCH row limiting clause (which includes an OFFSET clause).

Example

Using an Offset

Alternatives to the LIMIT Clause

Alternatives to the `LIMIT` Clause