MySQL LIMIT Clause – How to Use the LIMIT Clause

How to Use the MySQL LIMIT Clause

This tutorial explains how to use the MySQL LIMIT clause, which allows you to retrieve rows from one or more tables based on a specified number.

Why do You Need to Use MySQL LIMIT Clause?

Fetching rows from a large table may affect performance. It is not recommended to retrieve all rows from a table at once.

For example, suppose that the customer table has 1 million rows, and many client applications are trying to retrieve all those rows at once. It may be problematic for the database server to handle, as it will use many resources to retrieve the information and return the rows to the client application.

Of course, it depends on the database server. A high-performance server will return rows faster than a low-performance server.

However, it is a good practice to limit the number of rows in a large table to reduce the resources consumed by the server. To do so, you need to use the MySQL LIMIT clause in a SELECT statement.

LIMIT Clause Syntax

To limit the number of rows using the LIMIT clause, write a SELECT statement as follows:

SELECT * FROM table
LIMIT number_of_records

LIMIT Clause Examples

Suppose you have a table named orders, which consists of five columns: order_numbercustomer_idcustomer_nameamount, and status, as shown below:

order_numbercustomer_idcustomer_nameamountstatus
#SO_300001#CU_300001Jackie Harmon$1100.00Open
#SO_300002#CU_300002Iris Wilkerson$1250.80Confirmed
#SO_300003#CU_300003Josh Warner$1050.50Closed
#SO_300004#CU_300001Jackie Harmon$1980.00Open
#SO_300005#CU_300004Jessica Day$1870.50Open
#SO_300006#CU_300002Iris Wilkerson$450.00Confirmed
#SO_300007#CU_300003Josh Warner$780.00Closed
#SO_300008#CU_300004Jessica Day$880.50Canceled
#SO_300009#CU_300005Iris Wilkerson$850.50Canceled
#SO_300010#CU_300001Jackie Harmon$1140.00Open

Select only top rows without conditions

The following example returns only the top two rows of the orders table:

SELECT * FROM orders
LIMIT 2;

Select only the top rows for the specified condition

The following SQL statement returns only the top three rows of the orders table where customer_id is equal to #CU_200002:

SELECT * FROM orders
WHERE customer_id="#CU_300002"
LIMIT 3;

Even though the orders table contains more than three rows that meet the specified criteria, MySQL will only return the first three rows of the matched rows in the table.

If less than three rows meet the specified criteria, MySQL will return all of them.
If there are no rows in the table, MySQL returns 0.

You can also specify additional conditions, as in the following example:

SELECT * FROM orders
WHERE (customer_id="#CU_300002" OR customer_id="#CU_300001") AND status="Open"
LIMIT 3;

LIMIT and ORDER BY

You can also use the LIMIT and ORDER BY clauses to select only the top rows of a specified table sorted in ascending or descending order by a specified column, as shown in the following example:

SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 5.

In this example, MySQL will return only the top five rows sorted in descending order by column order_date.

Summary

In this tutorial, you have learned how to use the MySQL LIMIT clause. The LIMIT clause is useful when you want to limit the number of rows returned from a database.


See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment