MySQL LIMIT Clause – How to Use the LIMIT Clause


How to Use the MySQL LIMIT Clause

In this tutorial, you'll learn how to use the LIMIT clause in MySQL to retrieve records from one or more tables in a given database based on a specified number.

Why do You Need to Use MySQL LIMIT Clause?

Retrieving records from a large table can have an impact on performance. You should not retrieve all records from the table at once.

For example, let's say you have 1 million records in the customer table. And a lot of client applications are trying to retrieve all those records at once at the same time. So it might be a problem for a database server to handle because your database server will use a lot of resources to search for the information and then return records to client applications. 

Of course, it also depends on your database server, so a higher performance server returns records faster than a lower one.

However, it's a good practice to limit the number of records on a large table to reduce server consuming resources. That's why you need to use the MySQL LIMIT clause in the SELECT statement.

LIMIT Clause Syntax

To limit the number of records returned from the MySQL database using the LIMIT clause, you can write the 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 the Top Rows without Conditions

The following example retrieves only the top two rows of the orders table without conditions:

SELECT * FROM orders
LIMIT 2;

Select only the Top Rows with Specified Conditions

The following SQL statement returns only the top 3 rows of the orders table, which 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 condition, MySQL returns only the first three rows of the matched rows in the table.

If there are less than three rows that meet the specified condition, MySQL returns all those records.
MySQL returns 0 when there are no rows in the table.

You can also specify more conditions, as the following example shows:

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

ORDER BY and LIMIT

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

SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 5.

In the above example, MySQL returns only the top 5 rows sorted in descending order by the column order_date.

In this tutorial, you've learned how to use the MySQL LIMIT clause in the SELECT statement to limit the number of rows returned from the MySQL database.

Leave a Reply

Your email address will not be published. Required fields are marked *