intro
Let’s see what the SQL LIMIT clause is and how it allows you to control the number of records returned by your queries.
By default, databases return all records that match the criteria specified in a SELECT query. On large datasets, this can easily lead to performance issues. That is why databases such as MySQL and PostgreSQL provide the SQL LIMIT clause. This powerful clause enables you to control the number of rows returned by a query, offering precise control over result sets.
In this article, you will find out what the LIMIT SQL clause is, which databases support it, and how and when to use it. Time to set a limit on the number of records returned by your queries!
What Is the LIMIT SQL Clause?
The LIMIT SQL clause restricts the number of rows returned by a query. In detail, it allows you to define an upper limit on the number of records fetched by an SQL query.
Note that LIMIT is not part of standard SQL. It appears as a vendor extension to SQL in some databases, including MySQL, PostgreSQL, and others. Refer to the official documentation pages to learn more:
In particular, SQL Server and Oracle do not support LIMIT. The SQL Server LIMIT equivalent clause is TOP while the Oracle equivalent clause is OFFSET...FETCH.
OFFSET...FETCH comes from the SQL 2008 standard as a method to standardize query limiting and offsetting behavior and is also supported by SQL Server, MariaDB, and PostgreSQL.
How to Use LIMIT in SQL
This is the syntax of LIMIT in SQL dialects that support the clause:
1
SELECT select_list
2
FROM table_name
3
[ WHERE ... ]
4
[ ORDER BY ... ]
5
[ LIMIT number_records ] [ OFFSET number_offset ]
Where:
Note that a query can have an OFFSET clause without a LIMIT clause. However, the two clauses are typically used together in a single SQL LIMIT OFFSET query.
If number_records is 0, the LIMIT SQL query will always return an empty result set. Consider the sample query below:
1
SELECT *
2
FROM products
3
LIMIT 0;
This will always return an empty table, no matter how many rows the products table contains.
PostgreSQL also supports two special values for LIMIT:
Both produce the same results, changing the behavior of the query as if the LIMIT clause was not present at all. Assume you have the following PostgreSQL query:
1
SELECT *
2
FROM products
3
LIMIT ALL OFFSET 5;
That is equivalent to:
1
SELECT *
2
FROM products
3
OFFSET 5;
In both cases, the two queries will retrieve all rows from the products table starting from the sixth row.
If you try to use NULL or ALL in a MySQL query, you will get a syntax error. Similarly, number_offset can be NULL in PostgreSQL but not in MySQL. In PostgreSQL, setting number_offeset to NULL is equivalent to setting it to 0.
MySQL also supports an equivalent and more concise LIMIT OFFSET SQL syntax:
1
SELECT select_list
2
FROM table_name
3
[ WHERE ... ]
4
[ ORDER BY ... ]
5
[ LIMIT [ number_offset , ] number_records ]
For example, take a look at the MySQL query below:
1
SELECT *
2
FROM products
3
LIMIT 2, 5;
That is equivalent to:
1
SELECT *
2
FROM products
3
LIMIT 5
4
OFFSET 2
If you try to use the LIMIT x, y syntax in PostgreSQL, you will receive the following error:
1
[Code: 0, SQL State: 42601] ERROR: LIMIT #,# syntax is not supported
2
Hint: Use separate LIMIT and OFFSET clauses.
SQL Limit: Use Cases
Now that you know what databases support the LIMIT SQL clause and how it works, you are ready to explore its top three use cases.
Note: The sample queries in this section will be written in MySQL and executed in DbVisualizer, the database client with the highest user satisfaction in the market.
1. Implement SQL Pagination
Retrieving all the data in a table at once is slow and inefficient. Plus, displaying all the data on a single page leads to an overwhelming user experience. This is where SQL pagination comes in! That technique allows you to break large result sets into manageable chunks, which is crucial for web applications and APIs. SQL pagination is probably the most common use case for LIMIT.
Assume you have a web page displaying a list of blog posts. You want to display 10 posts per page, giving users the ability to navigate through multiple pages. Retrieve the blog posts for the page n with the following SQL LIMIT OFFSET query:
1
SELECT *
2
FROM posts
3
ORDER BY date_published DESC
4
LIMIT 10 OFFSET (n - 1) * 10;
Thus, you can retrieve the posts for the second page of your blog with:
1
SELECT *
2
FROM posts
3
ORDER BY date DESC
4
LIMIT 10 OFFSET 10;
The result will be:
2. Retrieve Top-N Records
Another common use case for LIMIT is to retrieve the top n records based on certain criteria, such as the highest sales, most recent activity, or highest-rated items.
Imagine you want to find the top 5 highest-paid employees in your company. This is how you can retrieve that information:
1
SELECT *
2
FROM employees
3
ORDER BY salary DESC
4
LIMIT 5;
Specifically, the SQL LIMIT 1 query is particularly popular because it enables you to get only the first row of a result set.
For example, suppose you wanted to retrieve the highest-paid engineer on your team. The query would be:
1
SELECT *
2
FROM employees
3
WHERE department = "Engineering"
4
ORDER BY salary DESC
5
LIMIT 1;
This time, the result will be:
3. Data Sampling
Data sampling involves selecting a random subset of data from a larger dataset for analysis or testing purposes. This technique is useful to quickly analyze a portion of your data without processing the entire dataset.
Now, suppose you want to analyze the demographics of a random sample of 100 customers from your database. You can get that data with the LIMIT query below:
1
SELECT *
2
FROM customers
3
ORDER BY RAND()
4
LIMIT 100;
The ORDER clause sorts the customer records randomly using the RAND() function, which assigns a random floating-point value between 0 and 1 to each row. By sorting the rows based on these random values, the set of results will be sorted randomly. Then, the LIMIT function limits the result set to 100 rows.
Best Practices of the LIMIT SQL Clause
Here are the most important best practices for the LIMIT SQL clause:
Conclusion
In this guide, you understood what the LIMIT clause is, how databases support it, and how it works. Now you know that the LIMIT clause helps you retrieve only a specific number of records from your queries. Thanks to the examples explored here, you also understood in what scenarios the SQL LIMIT clause comes in handy.
Writing complex queries becomes easier with a database client. DbVisualizer is a powerful database client that supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free today!
FAQ
Is there an SQL Server LIMIT clause?
No, SQL Server does not support the LIMIT clause. At the same time, it provides the same functionality via the TOP keyword and OFFSET... FETCH clause. See the documentation for more information.
How to get a single record with an SQL query?
You can get a single record from a query by using an SQL LIMIT 1 statement as follows:
1
SELECT *
2
FROM table_name
3
LIMIT 1;
The LIMIT clause can also define the starting and ending points. Here we count from 0, so if you want to return the second row counting from the top, your LIMIT clause would become LIMIT 1,1 instead of LIMIT 1 (count from 1, return 1.)
Does the LIMIT SQL clause have performance implications?
Yes, the LIMIT SQL clause can have performance implications. When used without proper consideration, especially on large datasets, it may impact query execution time and resource consumption. For efficient querying, it is essential to specify a reasonable limit and combine it with appropriate optimizations. Find out more in our guide on how to write efficient SQL queries.
How can an SQL LIMIT OFFSET query help you write a pagination query?
An SQL LIMIT OFFSET query can facilitate pagination operations by allowing you to retrieve a subset of results starting from a specific offset. This enables you to fetch data in manageable chunks. By adjusting the offset value based on the current page, you can navigate through paginated data efficiently.
What does an SQL LIMIT 1 query do?
An SQL LIMIT 1 query retrieves only the first row from a result set. It is commonly used to quickly check existence or fetch a single value from a table. That query ensures efficient and precise data retrieval for various applications and scenarios, such as finding the best match given some specific criteria.
