Limiting Results Using LIMIT and OFFSET in MySQL

Course: MySQL Training

Topic: Limiting Results Using LIMIT and OFFSET

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to LIMIT and OFFSET

In MySQL, the LIMIT clause is used to restrict the number of rows returned by a SELECT query. When a database table contains thousands or millions of records, displaying every record at once may be unnecessary. LIMIT allows developers to retrieve only the number of records required for a particular task.

OFFSET specifies how many rows to skip before returning the requested records. LIMIT and OFFSET are commonly used together for pagination, where data is divided into multiple pages on websites, dashboards, admin panels, reports, and applications.

For example, an online shopping website may contain thousands of products. Instead of displaying every product on one page, it can display 10 products per page and use OFFSET to retrieve the products for the next page.

Basic Syntax

SELECT column1, column2
FROM table_name
LIMIT row_count OFFSET offset_value;

• SELECT specifies the columns to retrieve.
• FROM specifies the table from which data is retrieved.
• LIMIT defines the maximum number of rows to return.
• OFFSET specifies the number of rows to skip before returning results.

Important: MySQL counts OFFSET from zero. OFFSET 0 means that no rows are skipped, while OFFSET 5 means that the first five rows of the ordered result are skipped.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the purpose of LIMIT in MySQL.
• Understand how OFFSET works.
• Retrieve a specific number of rows from a table.
• Combine LIMIT with ORDER BY and WHERE.
• Use LIMIT and OFFSET to implement pagination.
• Calculate the offset for different page numbers.
• Understand the difference between LIMIT and OFFSET.
• Use LIMIT with aggregate queries and joins.
• Recognize common mistakes when limiting query results.
• Apply pagination techniques to real-world database projects.

3. Preparing a Sample Database

To understand LIMIT and OFFSET, create a sample database and an employees table. If you already have a practice table, you can use that instead.

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50)
);

Insert sample records if these employee IDs are not already present in the table.

INSERT INTO employees
(employee_id, employee_name, department, salary, city)
VALUES
(101, 'Amit Sharma', 'IT', 55000, 'Mumbai'),
(102, 'Priya Verma', 'HR', 45000, 'Pune'),
(103, 'Rahul Singh', 'IT', 60000, 'Delhi'),
(104, 'Neha Patel', 'Finance', 50000, 'Mumbai'),
(105, 'Vikram Joshi', 'Sales', 40000, 'Nashik'),
(106, 'Sneha Gupta', 'HR', 48000, 'Pune'),
(107, 'Arjun Mehta', 'IT', 65000, 'Mumbai'),
(108, 'Pooja Shah', 'Finance', 52000, 'Delhi'),
(109, 'Karan Malhotra', 'Sales', 42000, 'Mumbai'),
(110, 'Anjali Desai', 'Marketing', 58000, 'Pune');

Check the sample records before running the following queries.

SELECT *
FROM employees
ORDER BY employee_id;

4. Understanding the LIMIT Clause

The LIMIT clause restricts the maximum number of rows returned by a query. When LIMIT is used without OFFSET, MySQL starts returning records from the beginning of the result set.

Example 1: Retrieve the First Five Employees

SELECT *
FROM employees
LIMIT 5;

This query returns a maximum of five records. Because the query does not include ORDER BY, the order of the returned records is not guaranteed.

Example 2: Retrieve Only Three Columns and Five Records

SELECT employee_id, employee_name, salary
FROM employees
LIMIT 5;

This query returns only the employee ID, employee name, and salary columns for a maximum of five rows.

Example 3: Retrieve the First Three Employees by ID

SELECT *
FROM employees
ORDER BY employee_id ASC
LIMIT 3;

ORDER BY sorts employees by employee_id in ascending order before LIMIT restricts the output. This produces a predictable selection when employee_id is unique.

5. Understanding the OFFSET Clause

OFFSET is used to skip a specified number of rows before returning results. It is particularly useful when a user wants to move from one page of results to another.

Example 1: Skip the First Three Employees

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 3;

This query skips the first three rows of the ordered result and returns up to five rows after them.

Example 2: Skip the First Five Employees

SELECT employee_id, employee_name
FROM employees
ORDER BY employee_id
LIMIT 3 OFFSET 5;

This query skips the first five employees and returns up to the next three employees.

Understanding OFFSET with a Simple Example

For a query using LIMIT 2 OFFSET 3, MySQL skips the first three rows and returns the fourth and fifth rows in the ordered result.

SELECT employee_id, employee_name
FROM employees
ORDER BY employee_id
LIMIT 2 OFFSET 3;

6. Using LIMIT with OFFSET Together

LIMIT and OFFSET are frequently combined to retrieve a particular section of a result set. LIMIT determines how many rows to return, and OFFSET determines where to begin within the ordered result.

Example 1: Retrieve Rows 1 to 5

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 0;

Example 2: Retrieve Rows 6 to 10

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 5;

Example 3: Retrieve Rows 11 to 15

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 10;

These queries demonstrate how the same LIMIT value can be combined with different offsets to retrieve different sections of a dataset.

7. Alternative LIMIT Syntax in MySQL

MySQL supports two common forms for specifying a row limit and an offset.

Syntax 1: LIMIT with OFFSET Keyword

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 10;

This returns up to five rows after skipping ten rows.

Syntax 2: LIMIT with Two Arguments

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 10, 5;

In the two-argument form, the first number is the offset and the second number is the row count. Therefore, LIMIT 10, 5 skips ten rows and returns up to five rows.

Best practice: The LIMIT row_count OFFSET offset_value form can be easier to read because the roles of the two numbers are explicit.

8. Using LIMIT with ORDER BY

ORDER BY sorts the result set, while LIMIT restricts the number of rows returned. Combining them is useful when you want the highest, lowest, newest, oldest, or otherwise ranked records.

Example 1: Find the Five Highest-Paid Employees

SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 5;

The query sorts employees by salary from highest to lowest and returns up to five records. The employee ID provides a tie-breaker when two employees have the same salary.

Example 2: Find the Three Lowest-Paid Employees

SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary ASC, employee_id ASC
LIMIT 3;

Example 3: Retrieve the Most Recent Orders

Assume an orders table contains order_id and order_date columns.

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

This query returns up to ten orders with the latest dates, with order_id used to break ties.

Important: Without ORDER BY, SQL does not guarantee a particular row order. Do not assume that LIMIT always returns the same rows when the query has no explicit ordering.

9. Using LIMIT with WHERE

WHERE filters rows based on a condition. LIMIT then restricts the number of rows returned from the filtered result set.

Example 1: Retrieve Five Employees from IT

SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'IT'
ORDER BY employee_id
LIMIT 5;

This returns up to five employees from the IT department.

Example 2: Retrieve Three Employees Earning More Than 50000

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC, employee_id ASC
LIMIT 3;

Example 3: Retrieve the Next Three Matching IT Employees

SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'IT'
ORDER BY employee_id
LIMIT 3 OFFSET 2;

This query filters the table to IT employees, orders the matching records, skips the first two matching rows, and returns up to the next three rows.

10. Calculating OFFSET for Pagination

Pagination divides a large result set into smaller pages. For example, an application may show ten products on each page rather than displaying every product at once.

The standard formula for calculating the offset is:

OFFSET = (page_number - 1) * page_size

• page_number is the page the user wants to view, starting from 1.
• page_size is the maximum number of rows displayed on each page.
• OFFSET is the number of rows to skip.

Pagination Calculation Example

Assume the page size is 10 records.

Example: Retrieve Page 3 with 10 Employees per Page

SELECT employee_id, employee_name, salary
FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 20;

This query skips the first 20 rows and returns up to the next 10 rows.

11. Implementing Pagination in a Web Application

Web applications commonly use LIMIT and OFFSET to display database records in pages. The application determines the page number and page size, calculates the offset, and executes a parameterized query.

Example Pagination Logic

page_number = 3
page_size = 10

offset = (page_number - 1) * page_size

SQL:
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 20;

The values in the example are fixed for demonstration. In a real application, validate user-provided page numbers and page sizes and use parameterized queries rather than concatenating untrusted input into SQL strings.

Displaying Page Information

An application can calculate the number of pages using the total number of matching records and the page size.

total_pages = CEILING(total_records / page_size)

For example, if a query matches 95 records and the page size is 10, the application needs 10 pages to display the full result set.

To calculate the total number of matching employees in MySQL, use:

SELECT COUNT(*) AS total_records
FROM employees;

If the page displays filtered results, the count query should use the same filtering conditions as the paginated SELECT query.

12. Using LIMIT with Aggregate Functions

LIMIT can also be used with aggregate queries. When the query includes GROUP BY, LIMIT restricts the number of groups returned after grouping and sorting.

Example 1: Show the Three Departments with the Most Employees

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC, department ASC
LIMIT 3;

This query groups employees by department, counts the employees in each department, sorts the groups by count, and returns up to three groups.

Example 2: Show the Two Highest Average Salaries by Department

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC, department ASC
LIMIT 2;

Here, LIMIT restricts the number of grouped department results. It does not restrict the number of employee rows considered by the aggregate calculation.

13. Using LIMIT with JOIN

A JOIN combines related data from multiple tables. LIMIT can restrict the number of rows returned by a joined query.

Example: Retrieve Five Orders with Customer Details

Assume the database contains customers and orders tables linked by customer_id.

SELECT
    o.order_id,
    o.order_date,
    c.customer_id,
    c.customer_name
FROM orders AS o
JOIN customers AS c
    ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC, o.order_id DESC
LIMIT 5;

This query returns up to five joined order-and-customer rows, ordered by the latest order dates and order IDs.

Remember that LIMIT applies to the rows returned by the overall joined query. If a join creates multiple rows for one parent record, LIMIT counts those output rows rather than distinct parent records.

14. LIMIT and OFFSET with DISTINCT

DISTINCT removes duplicate rows from the selected result. LIMIT and OFFSET can then restrict the number of distinct rows returned.

Example: Retrieve the First Three Distinct Cities

SELECT DISTINCT city
FROM employees
WHERE city IS NOT NULL
ORDER BY city
LIMIT 3 OFFSET 0;

This query returns up to three distinct, non-NULL city values in alphabetical order.

Example: Retrieve the Next Three Distinct Cities

SELECT DISTINCT city
FROM employees
WHERE city IS NOT NULL
ORDER BY city
LIMIT 3 OFFSET 3;

This skips the first three distinct cities and returns up to the next three.

15. LIMIT and OFFSET Performance Considerations

LIMIT can reduce the number of rows sent to an application, which is helpful when only a small result set is needed. MySQL may also optimize certain queries containing LIMIT, particularly when a suitable index supports the filtering and ordering.

However, a query with a large OFFSET may still need to find and skip many rows before returning the requested page. For example, retrieving a small page after skipping hundreds of thousands of rows may be less efficient than retrieving an early page.

Example of a Large OFFSET

SELECT employee_id, employee_name
FROM employees
ORDER BY employee_id
LIMIT 20 OFFSET 100000;

This query requests up to 20 rows after skipping 100,000 rows in the ordered result. Its actual performance depends on the query plan, indexes, table size, and data distribution.

Ways to Improve Performance

• Create appropriate indexes for frequently used filtering and ordering columns.
• Use ORDER BY with a unique tie-breaker to provide stable pagination ordering.
• Avoid retrieving unnecessary columns when only a few are required.
• Use EXPLAIN to inspect how MySQL plans to execute a query.
• Consider keyset pagination for large datasets when users move sequentially through results.
• Measure query performance on realistic data rather than assuming that LIMIT alone makes every query fast.

16. Keyset Pagination as an Alternative

Keyset pagination, also called cursor-based pagination, uses the last-seen key value instead of skipping a large number of rows. It can be more efficient for navigating large datasets when a suitable index supports the ordering.

Example: First Page

SELECT employee_id, employee_name
FROM employees
ORDER BY employee_id
LIMIT 10;

Example: Next Page After Employee ID 110

SELECT employee_id, employee_name
FROM employees
WHERE employee_id > 110
ORDER BY employee_id
LIMIT 10;

This example assumes employee_id is a unique, increasing key and that the next page should contain records with IDs greater than the last ID from the previous page.

Keyset pagination is not identical to OFFSET pagination. It is particularly useful for sequential navigation, but it does not directly provide arbitrary numbered-page access without additional work.

17. Common Mistakes When Using LIMIT and OFFSET

18. Best Practices for LIMIT and OFFSET

• Always define the required order: Use ORDER BY when the application needs predictable results.
• Use a unique tie-breaker: If sorting by a non-unique column, add a unique key such as employee_id.
• Calculate OFFSET correctly: Use (page_number - 1) × page_size for one-based page numbers.
• Validate pagination inputs: Ensure the page number is valid and the page size is within the application's allowed range.
• Keep page sizes reasonable: Avoid returning thousands of rows to an interface that displays only a small number.
• Use indexes where appropriate: Indexes may help MySQL filter and sort records efficiently.
• Consider changing data: Inserts and deletes between page requests can cause OFFSET-based pages to shift.
• Use keyset pagination when suitable: It can improve sequential navigation through very large datasets.
• Check query plans: Use EXPLAIN to understand the execution strategy for performance-sensitive queries.
• Test edge cases: Handle empty results, invalid pages, and the final page correctly.

19. Practical Exercises

Use the employees practice table to complete these exercises.

1. Retrieve the first five employees ordered by employee_id.
2. Retrieve only employee_name and salary for the first three employees.
3. Skip the first four employees and return the next three.
4. Find the five highest-paid employees.
5. Retrieve the second page when the page size is three.
6. Retrieve up to four IT employees, ordered by employee_id.
7. Find the three departments with the highest employee counts.
8. Retrieve the first three distinct cities in alphabetical order.
9. Write a query to return the next page of employees using employee_id as a cursor.
10. Use EXPLAIN to inspect a query containing WHERE, ORDER BY, and LIMIT.

Sample Solutions

-- 1. First five employees
SELECT *
FROM employees
ORDER BY employee_id
LIMIT 5;

-- 2. First three employees with selected columns
SELECT employee_name, salary
FROM employees
ORDER BY employee_id
LIMIT 3;

-- 3. Skip four employees and return the next three
SELECT *
FROM employees
ORDER BY employee_id
LIMIT 3 OFFSET 4;

-- 4. Five highest-paid employees
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 5;

-- 5. Second page with a page size of three
SELECT *
FROM employees
ORDER BY employee_id
LIMIT 3 OFFSET 3;

-- 6. Up to four IT employees
SELECT *
FROM employees
WHERE department = 'IT'
ORDER BY employee_id
LIMIT 4;

-- 7. Three departments with the highest employee counts
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC, department ASC
LIMIT 3;

-- 8. First three distinct cities
SELECT DISTINCT city
FROM employees
WHERE city IS NOT NULL
ORDER BY city
LIMIT 3;

-- 9. Keyset pagination after employee ID 105
SELECT employee_id, employee_name
FROM employees
WHERE employee_id > 105
ORDER BY employee_id
LIMIT 10;

-- 10. Inspect the execution plan
EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE department = 'IT'
ORDER BY employee_id
LIMIT 5;

20. Mini Project: Employee Directory with Pagination

Project Objective

Create a database query workflow for an employee directory that displays a limited number of employee records per page.

Project Requirements

• Use the employees table with employee IDs, names, departments, salaries, and cities.
• Choose a page size, such as five employees per page.
• Retrieve the first page using LIMIT.
• Retrieve later pages using LIMIT and OFFSET.
• Sort records by employee_id to provide a stable ordering.
• Allow the directory to filter employees by department.
• Calculate the total number of matching records using COUNT(*).
• Calculate the total number of pages in the application.
• Test behavior when the requested page is empty or beyond the final page.
• Compare OFFSET pagination with keyset pagination for sequential browsing.

Example: First Page of the Employee Directory

SELECT employee_id, employee_name, department, city
FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 0;

Example: Second Page of the Employee Directory

SELECT employee_id, employee_name, department, city
FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 5;

Example: Paginated Results for the IT Department

SELECT employee_id, employee_name, department, city
FROM employees
WHERE department = 'IT'
ORDER BY employee_id
LIMIT 5 OFFSET 0;

This project demonstrates how LIMIT and OFFSET can be used to retrieve manageable portions of database results for real applications.

21. MySQL LIMIT and OFFSET Interview Questions

Q1. What is LIMIT in MySQL?

Answer: LIMIT restricts the maximum number of rows returned by a query.

Q2. What is OFFSET in MySQL?

Answer: OFFSET specifies how many rows to skip before returning results.

Q3. What is the difference between LIMIT and OFFSET?

Answer: LIMIT controls the maximum number of rows returned, while OFFSET controls the number of rows skipped before the result begins.

Q4. What does LIMIT 5 OFFSET 10 mean?

Answer: It skips the first ten rows in the ordered result and returns up to five rows after them.

Q5. What does LIMIT 10, 5 mean in MySQL?

Answer: It skips ten rows and returns up to five rows. In this syntax, the first number is the offset and the second is the row count.

Q6. How do you retrieve the first ten records?

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 10;

Q7. How do you retrieve the third page with a page size of ten?

Answer: Calculate the offset as (3 - 1) × 10 = 20.

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 20;

Q8. Why is ORDER BY important when using LIMIT?

Answer: ORDER BY defines the result order. Without it, the database does not guarantee which rows appear first.

Q9. Can LIMIT be used with WHERE?

Answer: Yes. WHERE filters the matching rows, and LIMIT restricts how many of those rows are returned.

Q10. Can LIMIT be used with GROUP BY?

Answer: Yes. It can restrict the number of grouped results returned after grouping and ordering.

Q11. What is pagination?

Answer: Pagination divides a large result set into smaller pages that can be displayed separately in an application.

Q12. What is the OFFSET formula for pagination?

OFFSET = (page_number - 1) * page_size

Q13. What is a disadvantage of a very large OFFSET?

Answer: MySQL may need to process and skip many rows before returning the requested page, which can make deep pagination slower.

Q14. What is keyset pagination?

Answer: Keyset pagination retrieves records after a previously seen key value rather than skipping a large number of rows using OFFSET.

Q15. Does LIMIT guarantee that the same rows will appear on every request?

Answer: No. A deterministic ORDER BY helps, but changes to the underlying data between requests can still affect OFFSET-based pages.

22. Quick Revision Notes

• LIMIT 5 returns up to five rows from the beginning of the result.
• LIMIT 5 OFFSET 10 skips ten rows and returns up to five.
• LIMIT 10, 5 is another way to express an offset of ten and a row count of five.
• OFFSET starts at zero, not one.
• Pagination commonly uses the formula (page_number - 1) × page_size.
• ORDER BY should be used when the selected row order matters.
• A unique tie-breaker helps provide deterministic ordering.
• WHERE filters records before the final result is limited.
• LIMIT can be used with joins, DISTINCT, and grouped queries.
• Large OFFSET values can be expensive for deep pagination.
• Keyset pagination is a useful alternative for sequential navigation through large datasets.
• Use EXPLAIN and suitable indexes to investigate query performance.

23. Conclusion

LIMIT and OFFSET are essential MySQL features for controlling query result sizes and implementing pagination. LIMIT determines how many rows are returned, while OFFSET determines how many rows are skipped. Together, they help applications display data in manageable sections and retrieve selected portions of a larger dataset.

For reliable results, combine pagination with ORDER BY, calculate offsets correctly, validate user input, and consider performance when working with large tables. For applications that need to navigate very large datasets sequentially, keyset pagination may be a better choice than using a large OFFSET.

Continue Learning: Explore more database concepts through the MySQL Training Course.

Register for a Demo: Visit JustAcademy Course Demo Registration to learn more about the training.