SQL LIMIT Clause

The LIMIT clause of SQL is a tool to limit rows returned from a SQL query. Rather than displaying all the records from a table, it allows displaying only the rows that are needed. This is extremely useful with tables containing hundreds or thousands of rows, as it allows for easy management of the output and improves query performance. Rows can also be displayed using LIMIT with OFFSET to skip a specific number of rows and just pull the next set of rows. In this blog, you will understand the SQL LIMIT clause in detail, along with its syntax, use cases, and best practices.

Table of Contents:

• What is SQL LIMIT Clause?
• Syntax of the SQL LIMIT Clause
• SQL LIMIT with ORDER BY Clause
• SQL LIMIT with OFFSET
• SQL LIMIT with WHERE Clause
• SQL LIMIT to Find nth Highest or Lowest Value
• SQL LIMIT in Different Databases
  1. LIMIT Clause in MySQL
  2. LIMIT Clause in PostgreSQL
• Difference Between LIMIT and TOP in SQL
• Benefits of Using the LIMIT Clause in SQL Queries
• Challenges of Using the LIMIT Clause in SQL Queries
• Common Use Cases of SQL LIMIT Clause
• Conclusion

What is SQL LIMIT Clause?

The LIMIT clause in SQL can limit the number of rows returned in the final result set of a query. It allows a particular number of records to be returned rather than retrieving the entire table from the database. It makes testing queries and previewing data easier, and improves performance while processing large tables.

Some of the key features of a LIMIT clause in SQL:

1. Restricts Output: Restricts the number of rows being returned in the output.
2. Improves Performance: Makes queries faster and more efficient when working with a large number of records.
3. Works with OFFSET: Usually used with an OFFSET clause (to skip rows before fetching the result).
4. Database Support: Supported in MySQL, PostgreSQL, and SQLite.
5. Simple to Apply: It requires only a numeric value to define how many rows to return.

Master SQL – From Beginner to Pro

Interactive lessons, real-world projects, live Q&A, and a certificate to showcase your skills.

Explore Program

Syntax of the SQL LIMIT Clause

The SQL LIMIT clause is written at the end of the query to specify the number of rows that are to be returned in the result.
Syntax:

SELECT column_name1, column_name2, ...
FROM table_name
LIMIT number;

Explanation:

• SELECT column_name1, column_name2, … is used to choose the columns you want to display from the table.
• FROM table_name specifies the table from which the data is selected.
• LIMIT number tells SQL how many rows to return in the result set.

NOTE: Let’s create a sample table that we will be using throughout this blog to understand the working of the SQL LIMIT clause in SQL.

-- Creating the students table
CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(50),
    course_name VARCHAR(50),
    marks INT
);

-- Inserting sample data
INSERT INTO students (student_id, student_name, course_name, marks) VALUES
(1, 'Amit', 'Artificial Intelligence', 85),
(2, 'Riya', 'Data Science', 90),
(3, 'Karan', 'Cloud Computing', 76),
(4, 'Sneha', 'Full Stack Development', 88),
(5, 'Vikram', 'Data Science', 92),
(6, 'Anita', 'Cyber Security', 81),
(7, 'Arjun', 'Artificial Intelligence', 95),
(8, 'Meena', 'Full Stack Development', 87);

Output:

SQL LIMIT with ORDER BY Clause

When using the SQL LIMIT clause, you will often find that the ORDER BY clause is used as well. The ORDER BY clause sorts the data in an ascending or descending order, and the LIMIT clause gets a limited number of rows from that ordered result, so you can get the “top” records based on a particular column.

Example:

SELECT student_id, student_name, course_name, marks
FROM students
ORDER BY marks DESC
LIMIT 3;

Output:

Explanation: Here, the query first orders the students based on their marks (in descending order), and then the LIMIT 3 ensures that only the top three students with the most marks are displayed.

SQL LIMIT with OFFSET

The OFFSET keyword in SQL can be used to skip a certain number of rows before the result is returned. It is typically combined with LIMIT in SQL to fetch the data page by page, which is useful for pagination.

Example:

SELECT student_id, student_name, course_name, marks
FROM students
ORDER BY marks DESC
LIMIT 3 OFFSET 2;

Output:

Explanation: Here, the query will skip two students with the highest marks and display the next three students in the result.

SQL LIMIT with WHERE Clause

The SQL WHERE clause retrieves filtered records for a specific condition, and the SQL LIMIT clause restricts how many of those filtered rows should be returned. Using both these clauses together can be very helpful, particularly when a limited number of the matching records are needed instead of all of them.

Example:

SELECT student_id, student_name, course_name, marks
FROM students
WHERE course_name = 'Artificial Intelligence'
LIMIT 2;

Output:

Explanation: Here, the query first filters the students who are enrolled in the Artificial Intelligence course. Out of all the students, only 2 rows will be returned because of the LIMIT clause.

SQL LIMIT to Find nth Highest or Lowest Value

LIMIT can be used along with ORDER BY and OFFSET to obtain the nth highest or nth lowest value in a table. ORDER BY sorts the rows, OFFSET skips a number of rows, and LIMIT then retrieves the required number of rows.

Example:

SELECT student_id, student_name, course_name, marks
FROM students
ORDER BY marks DESC
LIMIT 1 OFFSET 2;

Output:

Explanation: Here, the query orders students by marks in descending order first, OFFSET 2 skips the top two, and LIMIT 1 retrieves only the third-highest marks.

SQL LIMIT in Different Databases

The LIMIT clause in SQL is not supported in all databases. Let’s now understand how it works in different databases and their alternatives for the databases that don’t support.

1. LIMIT Clause in MySQL

In MySQL, the LIMIT clause is used to limit the number of rows returned by a query. The LIMIT clause can also be used with OFFSET to limit the number of results returned and skip a certain number of records before returning rows.

Example:

SELECT student_name, marks
FROM students
ORDER BY marks DESC
LIMIT 3;

Output:

Explanation: Here, the query retrieves the top 3 students from the students table who have the highest marks.

2. LIMIT Clause in PostgreSQL

PostgreSQL supports both LIMIT and OFFSET clauses, like MySQL. LIMIT specifies how many rows to return, while OFFSET specifies how many rows to skip.

Example:

SELECT student_name, marks
FROM students
ORDER BY marks ASC
LIMIT 3 OFFSET 2;

Output:

Example: Here, the query retrieves 3 students with the lowest marks but skips the first 2 records, thus returning only the 3rd, 4th, and 5th lowest.

Note: Oracle and SQL Server do not support the SQL LIMIT clause. Oracle uses ROWNUM or FETCH FIRST n ROWS ONLY, and SQL Server uses TOP or OFFSET … FETCH NEXT for performing the same operation.

Difference Between LIMIT and TOP in SQL

Benefits of Using the LIMIT Clause in SQL Queries

1. Efficient Data Handling: It retrieves only the number of rows that are required instead of fetching the entire dataset, which helps in saving time.
2. Improved Query Performance: It helps in improving the execution of the query by avoiding the scanning of a large table completely. This helps in making the query more responsive.
3. Pagination Support: It works effectively with the OFFSET, which helps in fetching the data in smaller parts, making it useful for making web pages, dashboards, and reports.
4. Easy Testing & Debugging: Allows the developers to test the queries on small data, which helps in making the debugging faster.
5. Better User Experience: It displays the data in a clean and structured way by preventing the long results. This improves the user experience.

Challenges of Using the LIMIT Clause in SQL Queries

1. Incomplete Data View: The LIMIT clause only returns a portion of the table. Important rows may be left out if they are not combined with appropriate filtering or ordering.
2. Inconsistent Results: If the query is executed multiple times without using ORDER BY, the rows returned may differ each time the query is executed.
3. Scalability Issues: Using LIMIT on large data sets when there is a high OFFSET can degrade performance instead of improving it.
4. Limited Context: Users may miss out on the overall picture of their data because only a small portion of the data is displayed.
5. Excessive Querying: For accessing all the data, multiple queries are required, which increases the complexity of the applications.

Common Use Cases of SQL LIMIT Clause

1. Showing Top Records: Helps in displaying the top rows needed for finding the top 10 people getting the highest salary or the top 10 highest-selling products in a store.
2. Page-wise Display of Data: Helps in breaking larger results into smaller pages, which is very helpful in web applications, dashboards, and reports.
3. Debugging Queries: It helps the developers to test the queries on small sets of rows, like using LIMIT 5 on an employee table for checking if the salary details are fetched correctly.

Kickstart Your SQL Journey – 100% Free

Structured lessons, real query practice, and solid foundations at zero cost.

Explore Program

Conclusion

The SQL LIMIT statement is a simple yet powerful tool for developers and analysts for restricting the number of rows returned by a query. It helps improve speeds, simplify data retrieval, development, and troubleshooting, and also generates top results, page-wise views, and facilitates easy debugging of queries. While MySQL and PostgreSQL support SQL LIMIT directly, other databases do not. Databases such as Oracle and SQL Server will use different functions like ROWNUM, FETCH, and TOP for performing similar tasks. LIMIT can also be used in conjunction with clauses such as ORDER BY, OFFSET, and WHERE, which provide better control over the query and enhance the user experience. 

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

SQL LIMIT CLAUSE – FAQs