SQL Limit: What It Is & How To Use It

As you dive deeper into the world of SQL, one tool that you’ll find incredibly handy is the SQL LIMIT clause. Whether you’re a seasoned developer or a newbie just starting out, understanding and effectively using the LIMIT clause can significantly enhance your data-handling capabilities.

The SQL LIMIT clause is a versatile tool that restricts the number of records returned by a query. It’s commonly used in creating pagination features for websites and applications, allowing for the division of large datasets into manageable chunks. When combined with the OFFSET statement, it provides precise control over which subset of data to work with, enhancing flexibility in data handling.

In this article, we’ll delve into the SQL LIMIT clause, a crucial tool that enables you to control the number of records retrieved from a database.

From understanding its fundamental syntax to exploring its potential use cases, we’ll take an in-depth look at how this statement can enhance the efficiency and precision of your data-handling tasks.

Let’s get started!

What is SQL LIMIT?

Imagine this: You’re working with a vast database, thousands or even millions of records deep. Scrolling through such an extensive list to find specific data points can be like finding a needle in a haystack, not to mention time-consuming.

This is where the LIMIT clause comes to the rescue. It allows you to restrict the number of records returned by a query, making your data exploration more focused and manageable.

But that’s not all. The SQL LIMIT clause is also incredibly useful when you need to divide your data into manageable chunks.

Say, for instance, you’re developing a pagination feature for a website or application. Instead of overwhelming the system by loading all data at once, you can use the LIMIT clause to retrieve a specified, limited number of records for each page.

Mastering the limit keyword and its use can make your SQL journey smoother and more enjoyable.

The SQL LIMIT clause is a powerhouse when it comes to data retrieval operations. Its functionalities extend beyond simple record limitation, offering dynamic usage in conjunction with other SQL commands.

SQL LIMIT Clause Syntax

Different database management systems (DBMS) have various syntax structures for applying the LIMIT clause. In MySQL/PostGreSQL, the LIMIT syntax is as follows:

SELECT column_name(s) FROM table_name WHERE condition LIMIT number;

Here’s a use case so you can see it working on a sample table:

If you want to skip a certain number of rows, you can use the OFFSET keyword. The syntax is as follows:

SELECT * FROM table_name LIMIT number_of_rows OFFSET number_to_skip;

LIMIT is used to set an upper limit on the number of rows returned by a SELECT statement. While not all SQL versions support this clause, its primary purpose is to regulate the number of tuples returned to avoid fetching large amounts of data, thus improving performance.

The LIMIT and OFFSET clauses can be used together to achieve the desired result set. The general syntax for these clauses is as follows:

SELECT column_list
FROM table_name
ORDER BY column_order
LIMIT row_count
OFFSET offset_value;

Where row_count is a non-negative integer specifying the maximum number of rows to be returned, and offset_value is also a non-negative integer indicating the starting point from which rows should be fetched.

Example:

For SQL Server, instead of using the LIMIT clause, the TOP keyword is utilized to achieve the same result:

SELECT TOP row_count column_list
FROM table_name
ORDER BY column_order;

Here’s how it’ll look in SQL Server:

For pagination purposes in SQL Server 2012+, the OFFSET and FETCH clauses can be used:

SELECT column_list
FROM table_name
ORDER BY column_order
OFFSET offset_value ROWS
FETCH NEXT row_count ROWS ONLY;

It is important to note that using the LIMIT clause in conjunction with the OFFSET clause efficiently enables data pagination while avoiding the retrieval of unnecessary rows, enhancing overall query performance. You can see this scenario play out here:

How to Use SQL Limit in Different DBMS?

The basic syntax aside, this section focuses on different ways you can use SQL LIMIT to filter data from your table.

Please note: Not all database systems support the same exact syntax. You can see varieties of its use in SQL Server, MySQL, and Oracle in the below SQL statement examples.

1. Using SQL TOP in SQL Server

For example, if you want to select the top 5 records from a table named Employees, you can use the following query:

SELECT TOP 5 *
FROM Employees;

Additionally, you can use the PERCENT keyword to retrieve a percentage of rows instead of specifying an absolute number:

SELECT TOP number PERCENT column_name(s)
FROM table_name
WHERE condition;

This query will return the specified percentage of rows from the table, based on the given condition. If you wanted to retrieve the top 20% of employees with the highest salaries, your query would look like the following:

SELECT TOP 20 PERCENT *
FROM Employees
ORDER BY Salary DESC;

In this case, since we have 10 employees, the query will return the 2 employees with the highest salaries. If there were a fractional number of employees to return, SQL Server always rounds up to the next whole number.

2. Using SQL Limit in MySQL

In MySQL, LIMIT is used to restrict the number of rows returned from a result set. This can be helpful for keeping your query results concise or for better performance when dealing with large data sets.

LIMIT works in conjunction with the SELECT statement and can be used alongside other clauses such as WHERE and ORDER BY.

The basic syntax for a LIMIT clause in MySQL is the following syntax:

SELECT column_list FROM table_name WHERE condition LIMIT number;

• column_list: This represents the columns you want to retrieve from the database.
• table_name: This is the name of the table in the MySQL database that you want to query.
• condition: This is the filter or criteria that defines which rows to retrieve from the table.
• number: This is the maximum number of rows you want to return in the result set.

For example, if you want to retrieve the first three records from a Customers table, you would use the following query:

SELECT * FROM Customers LIMIT 3;

In addition to using the LIMIT clause with a single number, you can also use an OFFSET value to specify the starting row for retrieving data:

SELECT column_list FROM table_name WHERE condition LIMIT number OFFSET offset_value;

• offset_value: This is the number of rows to skip before starting the data retrieval.

For instance, if you want to retrieve five records from a Contacts table, starting from the sixth record, you would use the following query:

SELECT contact_id, last_name, first_name FROM Contacts WHERE website = 'example.com' ORDER BY contact_id DESC LIMIT 5 OFFSET 5;

This query would select the second set of five records from the Contacts table where the website is ‘example.com’ and order them by contact_id in descending order:

Here, the SELECT statement is used to select the ContactID, LastName, and FirstName columns from the Contacts table where the Website is ‘example.com’.

The ORDER BY ContactID DESC clause sorts the results in descending order by contact id. The OFFSET 5 ROWS clause skips the first 5 rows, and the FETCH NEXT 5 ROWS ONLY clause limits the results to the next 5 rows.

Using LIMIT effectively can lead to more efficient queries and better overall performance for your MySQL database. Just remember to use it carefully and in conjunction with other relevant clauses to get the most out of your queries and you can test or generate them with ChatGPT too!

3. Using LIMIT in Oracle

In Oracle SQL, you can use ROWNUM to restrict the number of rows returned by a query.

However, it’s important to note that ROWNUM is assigned before the ORDER BY clause is applied, which can lead to unexpected results if you’re trying to get the “top N” rows in ordered results.

Here’s an example:

SELECT *
FROM (SELECT ContactID, LastName, FirstName
      FROM Contacts
      WHERE Website = 'example.com'
      ORDER BY ContactID DESC)
WHERE ROWNUM <= 5;

In this query, the subquery orders the results first, and then ROWNUM is applied to the ordered results.

Starting with Oracle 12c, you can also use the FETCH FIRST clause to reduce the number of rows returned by a query, which is more intuitive and less prone to mistakes:

SELECT ContactID, LastName, FirstName
FROM Contacts
WHERE Website = 'example.com'
ORDER BY ContactID DESC
FETCH FIRST 5 ROWS ONLY;

In this query, FETCH FIRST 5 ROWS ONLY fetches the top 5 rows after ordering, which is equivalent to LIMIT 5 in MySQL or TOP 5 in SQL Server.

So, while both ROWNUM and FETCH FIRST can be used to limit the number of rows in Oracle SQL, FETCH FIRST is generally easier to use correctly and more in line with other SQL dialects.

The FETCH clause allows you to apply a limit on the number of rows returned after ordering the results. To use it, you need to combine it with an ORDER BY clause. For example:

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

This query will return the top 10 employees with the highest salaries. The FETCH FIRST N ROWS ONLY clause ensures that only the first N rows, in this case, 10, are returned after sorting the results based on the salary column.

Another approach to limit rows in Oracle is using the ROW_NUMBER() function combined with the OVER() clause. This method allows you to assign row numbers within a result set after specifying the desired order. Here’s an example:

SELECT *
FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
  FROM employees e
)
WHERE row_num <= 10;

In this query, the ROW_NUMBER() function assigns a unique number to each row, sorted by the salary column in descending order. Then, the outer query filters the result set by the row_num value to get the first 10 rows.

How to Use SQL Limit with Subqueries

In SQL, a subquery is a query that is embedded within another query, usually to retrieve intermediate results. Subqueries can be an essential tool when used correctly, as they allow you to filter, sort, and manipulate your data in powerful ways.

The use of the LIMIT clause in subqueries can be quite tricky and often depends on the specific database management system (DBMS) you’re using. When working with subqueries, LIMIT can be particularly useful for managing large datasets and improving the performance of your queries.

One way to use select LIMIT statement with subqueries is to apply it within the subquery itself. This can help you retrieve a limited set of intermediate results, which can then be used further in the outer query. For example, consider the following SQL code:

SELECT *
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM sales
    ORDER BY sale_date DESC
    LIMIT 5);

In this example, the subquery retrieves the product IDs for the five most recent sales, and the outer query returns information about those products.

Another use case is when you want to apply a LIMIT based on the result of a subquery. For instance, consider a scenario where you want to retrieve the top 10% of products based on their sales. You can achieve this by using a subquery within the LIMIT clause:

SELECT *
FROM products
ORDER BY sales_volume DESC
LIMIT (
    SELECT CEILING(COUNT(*) * 0.1)
    FROM products);

In this case, the subquery calculates the number of rows corresponding to the top 10% of the products and uses that value to set the ROW_COUNT for the LIMIT clause.

Final Thoughts

There’s a lot to consider when using the SQL limit, be it checking whether you need to use the where clause, when and where, or even how to use and most importantly, keep your code optimized. Moreover, working with different databases and how it’s going to change the basic syntax of our clause is something you need to keep in mind all the time.

We’ve covered the major DBMS systems like Oracle, MySQL, PostgreSQL, and SQL Server here, but it’s up to you to make sure you use all the syntax and filter data properly across each system. Understanding how and when to use the WHERE clause in conjunction with LIMIT, for instance, is crucial in ensuring that your queries return the expected results.

Working with different database management systems (DBMS) often means adapting to the nuances of their syntax, especially since the implementation of LIMIT varies from one DBMS to another. As we have discussed, Oracle, MySQL, PostgreSQL, and SQL Server each have their unique ways of handling the LIMIT clause.

Being aware of these differences is key to writing portable SQL code that works across different systems. And of course, if you’re getting stuck at places where you get the “developer block”, you can use generative AI like ChatGPT to interpret and analyze your code and data like in our tutorial:

Frequently Asked Questions

What is Limit 2 1 in SQL?

When you see LIMIT 2, 1 in an SQL query, it means that the query will return 1 row starting from the 3rd row (because the count starts from 0). In other words, LIMIT 2, 1 skips the first 2 records and then returns the next record.

For example, consider the following SQL statement:

SELECT * FROM your_table LIMIT 2, 1;

This statement would skip the first 2 records from your_table and then return the next record.

Is LIMIT 1 faster in SQL?

Yes, adding LIMIT 1 to your SQL query can make it faster. When you use LIMIT 1, the SQL engine knows it only needs to return one record, so it can stop scanning the table or index as soon as it finds the first match. This can significantly reduce the amount of data that needs to be processed, especially in large tables.

What is LIMIT 0 in SQL?

When you use LIMIT 0 in an SQL query, it means that the query will return zero rows. It doesn’t matter how many rows match the criteria in your query; with LIMIT 0, no rows will be returned.

Here’s an example:

SELECT * FROM your_table LIMIT 0;

This query would return an empty set, regardless of how many rows are in “your_table”.

What is OFFSET 1 in SQL?

When you use OFFSET 1, it means that the query will skip the first record and start returning rows from the second record onwards.

This can be particularly useful when you want to skip a certain number of records, for instance, in scenarios like data pagination. Here’s an example:

SELECT * FROM your_table ORDER BY some_column OFFSET 1 ROWS;

This SQL statement would skip the first record in your_table and return all the remaining records, ordered by some_column. Note that OFFSET is often used with an ORDER BY clause to determine the order of the records before skipping any.

However, do remember that the numbering starts from 0, so OFFSET 1 skips the first row.