The LIMIT clause specifies the maximum number of records to return in a query.
The LIMIT clause is particularly useful for optimizing performance on large tables with numerous records.
SELECT column_name(s) FROM table_name WHERE condition LIMIT number; |
Here is an excerpt from the “Customers” table in the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4 |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
The following SQL statement retrieves the first three records from the “Customers” table:
SELECT * FROM Customers LIMIT 3; |
What if we want to select records 4 to 6 (inclusive)?
MySQL allows this using the OFFSET clause.
The SQL query below specifies “return only 3 records, starting from record 4 (OFFSET 3)”:
SELECT * FROM Customers LIMIT 3 OFFSET 3; |
The following SQL statement retrieves the first three records from the “Customers” table where the country is “Germany”:
SELECT * FROM Customers WHERE Country=‘Germany’ LIMIT 3; |