How to Best Use the SQL LIMIT Clause

The SQL LIMIT clause controls the number of records returned in a query. As the name suggests, the LIMIT clause restricts the rows shown after filtering data. This clause is important when handling large datasets, but you only want to retrieve specific data portions.

If you are an aspiring data practitioner, I highly recommend taking DataCamp’s Introduction to SQL and SQL Fundamentals courses to gain relevant skills in SQL for data analysis.

The Short Answer: What is the SQL LIMIT Clause?

The SQL LIMIT clause is a useful clause that specifies the number of records a query should return after filtering data. This technique allows one to return only a subset of data for display or analysis. Therefore, the LIMIT clause is important for breaking down large data into smaller chunks in data pagination. It is also used for performance optimization to avoid large queries that could slow down database performance.

The example below shows the basic use of the LIMIT clause to show only the top 10 records from the employees table.

-- Select all columns from the employees table
-- and limit the number of returned rows to 10.
SELECT * 
FROM employees
LIMIT 10;

Syntax of the SQL LIMIT Clause

The SQL LIMIT clause is included in the SELECT statement to control the number of records returned from a query. The LIMIT clause is added at the end of the statement to filter the records as specified. You can also check DataCamp’s SQL Basics Cheat Sheet to learn more about the LIMIT and other common SQL clauses.

-- Select columns column1, column2, ... from the table table_name
SELECT column1, column2, ...
FROM table_name
-- Limit the number of rows returned to number_of_rows
LIMIT number_of_rows;

Here is the LIMIT clause in a practical example. The query will show only 10 records of the employees table from the first_name, last_name, and department columns.

-- Select first_name, last_name, and department from the employees table
SELECT first_name, last_name, department
FROM employees
-- Limit the number of rows returned to 10
LIMIT 10;

Sometimes, you can use the optional OFFSET clause to skip a specific number of rows before displaying the intended rows. The OFFSET syntax is shown below.

-- Select column_names from the table table_name
SELECT column_names
FROM table_name
-- Arrange the results in ascending order based on column_name
ORDER BY column_name
-- Limit the number of rows returned to number_of_rows, starting from offset_value
LIMIT number_of_rows OFFSET offset_value;

In the above SQL query, the number_of_rows indicates the number of rows to return, while the offset_value shows the number of rows the query should skip before returning them. 

The example below shows how to use SQL OFFSET and LIMIT clauses to control the number of rows to return. While the LIMIT clause specifies returning 10 records, the OFFSET clause in the query skips the first 5 records. Therefore, the results will display records from the 6th row of the data to the 15th row (10 rows).

-- Select first_name, last_name, and department from the employees table
SELECT first_name, last_name, department
FROM employees
-- Limit the number of rows returned to 10, starting from the 6th row (offset of 5)
LIMIT 10 OFFSET 5;

Important Points to Remember About the SQL LIMIT Clause

Understanding how to properly implement the LIMIT clause and its variations across different SQL databases is crucial for optimizing query performance and ensuring accurate data retrieval. Let's take a look at some important points. 

The LIMIT clause placement in the SELECT statement

The LIMIT clause is usually placed at the end of the SELECT statement after the FROM clause. If you use other optional aggregation clauses such as WHERE, GROUP BY, and ORDER BY clauses, ensure the LIMIT clause is at the end of the query.

-- Select all columns from the employees table
SELECT *
FROM employees
-- Limit the number of rows returned to 5, starting from the 21st row (offset of 20)
LIMIT 10 OFFSET 20;

Combining LIMIT with ORDER BY clause

The LIMIT clause can be combined with the ORDER BY aggregate for consistent and predictable results. This technique ensures the data are stored in a particular order before retrieving them. In the example below, we order the data by the hire_date in descending order before showing the filtered rows.

-- Select all columns from the employees table
SELECT *
FROM employees
-- Arrange the results in descending order based on hire_date
ORDER BY hire_date DESC
-- Limit the number of rows returned to 10
LIMIT 10;

I would recommend taking DataCamp’s Associate Data Analyst in SQL career track and Reporting in SQL course to learn more about aggregate functions and their applications in data analysis.

SQL LIMIT variations and syntax across databases

The above examples show the use of SQL LIMIT clauses in supported PostgreSQL, SQLite, and MySQL databases.

To achieve similar results in SQL Server, consider using the TOP function to control the number of records to return from given data.

-- Select the top 10 records of first_name, last_name from the employees table
SELECT TOP 10 first_name, last_name
FROM employees
-- Arrange the results in descending order based on hire_date
ORDER BY hire_date DESC;

Similarly, the Oracle database supports using FETCH FIRST n ROWS ONLY to control the number of records to return in a query.

-- Select the first_name and last_name columns from the employees table
SELECT first_name, last_name
FROM employees
-- Order the results in descending order based on hire_date
ORDER BY hire_date DESC
-- Limit the number of rows returned to the first 10 rows
FETCH FIRST 10 ROWS ONLY;

LIMIT and OFFSET in pagination

SQL LIMIT and OFFSET are usually combined to achieve efficient data pagination. This technique ensures large datasets are broken down into smaller, manageable chunks for appropriate display.

In the example below, the LIMIT and OFFSET clause limits the displayed rows to 10 but starts from the 21st row.

-- Select all columns from the employees table
SELECT *
-- Arrange the results in descending order based on hire_date
FROM employees
ORDER BY hire_date DESC
-- Limit the number of rows returned to 10, starting from the 21st row (offset of 20)
LIMIT 10 OFFSET 20;

Common Use Cases of the SQL LIMIT Clause

• Pagination: When combined with the OFFSET clause, the LIMIT clause is used in web applications for data pagination. This technique displays data in smaller chunks/subsets, such as on different web pages.
• Optimizing Performance: The LIMIT clause can improve query performance by reducing the load on the database servers. It achieves this technique by fetching and returning smaller data subsets simultaneously.
• Data Sampling: The LIMIT clause is useful in data analysis by fetching smaller data subsets. This method helps data analysts to improve performance during analysis.

Limitations of the SQL Limit Command

The limitations of the SQL LIMIT clause include the following:

• Lack of Support by All Databases: The LIMIT clause is not supported in all databases. For instance, the LIMIT clause is unavailable in SQL Server and Oracle databases. 
• Performance Issues with Large OFFSET values: Using large OFFSET values can slow down database servers, resulting in slower query execution.

To avoid the limitations of different databases, consider using the following alternatives.x

The TOP clause in SQL Server

The TOP clause gives the same results as the LIMIT clause for the SQL Server database.

-- Select the top 10 rows from the employees table ordered by hire_date
SELECT TOP 10 first_name, last_name
FROM employees
ORDER BY hire_date DESC;

FETCH FIRST n ROWS ONLY in SQL Server and DB2

The FETCH FIRST n ROWS ONLY also returns results similar to those of the LIMIT clause for the SQL Server and DB2 databases.

-- Select the first 10 rows from the employees table ordered by hire_date
SELECT first_name, last_name
FROM employees
ORDER BY hire_date DESC
FETCH FIRST 10 ROWS ONLY;

ROWNUM in Oracle

In the Oracle database, the ROWNUM function controls the number of records returned from data. The function is combined in a subquery to filter the data as required.

-- Select the top 10 rows from the employees table ordered by hire_date
SELECT * FROM (
    SELECT first_name, last_name
    FROM employees
    ORDER BY hire_date DESC
) WHERE ROWNUM <= 10;

Conclusion

The SQL LIMIT clause is important for restricting the number of rows displayed after retrieving data from a database. As a data practitioner, it is important to understand the different use cases of the LIMIT clause for data analysis and manipulation. Similarly, you should consider the database support for the LIMIT clause and use other alternative functions when using incompatible databases like SQL Server and Oracle.

To understand the different applications of the SQL LIMIT clause, you should continue practicing with different datasets. DataCamp’s Learn SQL and Intermediate SQL courses are appropriate for gaining advanced skills in SQL. I also recommend taking DataCamp’s SQL Associate Certification to demonstrate your knowledge of using SQL and stand among other data practitioners.