The LIMIT clause limits the maximum number of rows returned by a SELECT statement.
Other databases can have different syntaxes such as SELECT TOP, FETCH FIRST and WHERE ROWNUM.
Some databases also allow a LIMIT clause on INSERT and DELETE statements which limits the effect of those statements to the specified maximum amount of rows.
SELECT id, name_first, name_last
FROM users
LIMIT 3;
Without an ORDER BY clause, rows have no default sorting order in most databases. This means that using a LIMIT clause without ORDER BY can produce inconsistent result sets. It is best practice to always specify ORDER BY in your queries, especially when you are limiting the result set.
SELECT id, name_first, name_last
FROM users
ORDER BY id ASC
LIMIT 3;
OFFSET can be applied to a LIMIT to offset the results returned. This helps in scenarios where you need to return your results in smaller paginated batches instead of all the data at once.
SELECT id, name_first, name_last
FROM users
LIMIT 3
OFFSET 3;
| id | name_first | name_last |
|----|------------|-----------|
| 4 | Priya | Amini |
| 5 | Zara | Amini |
| 6 | James | Mitchell |
If your LIMIT reaches past the total amount of data to return, the query will just return the total amount of data as if a LIMIT was not applied.
SELECT id, name_first, name_last
FROM users
LIMIT 100;
If an OFFSET reaches past the total amount of data to return, the query will return no data.
SELECT id, name_first, name_last
FROM users
LIMIT 1
OFFSET 100;
| id | name_first | name_last |
|----|------------|-----------|
You can compute the number you supply to a LIMIT clause using arithmetic, Functions, Subqueries and other computations as long as the result is a valid numeric type. See your database's documentation for types compatible with LIMIT.
SELECT id, name_first, name_last
FROM users
LIMIT 2*2;
Sometimes but not always, adding a LIMIT clause to your query can improve performance. There are a number of factors that influence this including: how complex your query is, how many tables are queried, what Indexes are present on the table, and whether any rows actually match your query or if the query needs to scan the entire table anyways. Fewer results also means the size of the data returned over a network connection is less.