How to use the LIMIT clause in MySQL

The LIMIT clause is a powerful tool in MySQL that allows you to control the number of records retrieved from a query. Whether you need to paginate data, display a limited number of results, or optimize query performance, understanding how to use the LIMIT clause effectively is important.

Syntax

The LIMIT clause in MySQL restricts the number of rows returned by a SELECT statement. It has two variations:

Syntax 1

In this syntax, you specify only the maximum number of rows to be returned.

Syntax 2

In this syntax, you provide both an offset (the number of rows to skip) and the maximum number of rows to retrieve.

Retrieving a subset of rows

One common case is when you want to retrieve a specific number of rows from a table. Let’s consider a sample table called employees with columns id, name, and salary. To fetch the first five employees with the highest salaries, you can use the following query:

SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

This query retrieves the top five rows from the employees table, sorted by the salary column in descending order.

Implementation

First, you will need to create a table. To create a table to run the SQL query mentioned, you can use the following example schema

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);

Now, you have to insert some data in the table so that we can run our query on that data and see the final result.

INSERT INTO employees (id, name, salary) VALUES
(1, 'John Doe', 50000.00),
(2, 'Jane Smith', 60000.00),
(3, 'David Johnson', 75000.00),
(4, 'Sarah Williams', 55000.00),
(5, 'Michael Brown', 80000.00),
(6, 'Emily Davis', 65000.00),
(7, 'Robert Wilson', 70000.00),
(8, 'Olivia Martinez', 72000.00),
(9, 'William Anderson', 68000.00),
(10, 'Sophia Thompson', 67000.00);

Now, let's run our limit query.

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);
INSERT INTO employees (id, name, salary) VALUES
(1, 'John Doe', 50000.00),
(2, 'Jane Smith', 60000.00),
(3, 'David Johnson', 75000.00),
(4, 'Sarah Williams', 55000.00),
(5, 'Michael Brown', 80000.00),
(6, 'Emily Davis', 65000.00),
(7, 'Robert Wilson', 70000.00),
(8, 'Olivia Martinez', 72000.00),
(9, 'William Anderson', 68000.00),
(10, 'Sophia Thompson', 67000.00);
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

Pagination with LIMIT and OFFSET

Pagination is often required when displaying large result sets. By combining the LIMIT and OFFSET clauses, you can easily implement pagination. The OFFSET value specifies the starting point for retrieving rows.

To display results 11 to 20 from the employees table, you can use the following query:

SELECT * FROM employees LIMIT 10 OFFSET 10;

This query skips the first ten rows (OFFSET 10) and retrieves the next ten rows (LIMIT 10) from the table.

Implementation

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);
INSERT INTO employees (id, name, salary) VALUES
(1, 'John Doe', 50000.00),
(2, 'Jane Smith', 60000.00),
(3, 'David Johnson', 75000.00),
(4, 'Sarah Williams', 55000.00),
(5, 'Michael Brown', 80000.00),
(6, 'Emily Davis', 65000.00),
(7, 'Robert Wilson', 70000.00),
(8, 'Olivia Martinez', 72000.00),
(9, 'William Anderson', 68000.00),
(10, 'Sophia Thompson', 67000.00),
(11, 'Daniel Taylor', 54000.00),
(12, 'Ava Jackson', 61000.00),
(13, 'Joseph White', 73000.00),
(14, 'Mia Garcia', 57000.00),
(15, 'James Lee', 78000.00),
(16, 'Charlotte Rodriguez', 69000.00),
(17, 'Alexander Martinez', 71000.00),
(18, 'Abigail Harris', 71000.00),
(19, 'Ethan Clark', 66000.00),
(20, 'Amelia Lewis', 64000.00),
(21, 'Benjamin Hall', 53000.00),
(22, 'Elizabeth Young', 59000.00),
(23, 'Henry King', 76000.00),
(24, 'Sofia Turner', 56000.00),
(25, 'Andrew Scott', 79000.00),
(26, 'Grace Walker', 70000.00),
(27, 'Christopher Hill', 71000.00),
(28, 'Victoria Green', 70000.00),
(29, 'Davidson Reed', 67000.00),
(30, 'Harper Baker', 63000.00);
SELECT * FROM employees LIMIT 10 OFFSET 10;

Sorting with LIMIT

The LIMIT clause can be used in conjunction with the ORDER BY clause to sort the result set and retrieve a limited number of rows. Let’s say we want to fetch the three employees with the lowest salaries from the employees table:

SELECT * FROM employees ORDER BY salary ASC LIMIT 3;

This query sorts the rows in ascending order based on the salary column and retrieves the first three rows.

Implementation

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);
INSERT INTO employees (id, name, salary) VALUES
(1, 'John Doe', 50000.00),
(2, 'Jane Smith', 60000.00),
(3, 'David Johnson', 75000.00),
(4, 'Sarah Williams', 55000.00),
(5, 'Michael Brown', 80000.00),
(6, 'Emily Davis', 65000.00),
(7, 'Robert Wilson', 70000.00),
(8, 'Olivia Martinez', 72000.00),
(9, 'William Anderson', 68000.00),
(10, 'Sophia Thompson', 67000.00);
SELECT * FROM employees ORDER BY salary ASC LIMIT 3;

Performance optimization

The LIMIT clause can also contribute to optimizing query performance. By limiting the number of rows retrieved, you can significantly reduce the amount of data processed.

Additionally, you can combine the LIMIT clause with an appropriate WHERE clause to filter the result set before applying the limit. This approach can reduce the execution time and improve overall query performance.

Example

Let’s consider an example.

SELECT * FROM employees WHERE name = 'John Doe' ORDER BY id DESC LIMIT 5;

This query filters the result set based on the employees_name, sorts the rows in descending order by id, and retrieves the first five rows.

Implementation

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);
INSERT INTO employees (id, name, salary) VALUES
(1, 'John Doe', 50000.00),
(2, 'Jane Smith', 60000.00),
(3, 'David Johnson', 75000.00),
(4, 'Sarah Williams', 55000.00),
(5, 'Michael Brown', 80000.00),
(6, 'John Doe', 65000.00),
(7, 'Robert Wilson', 70000.00),
(8, 'Olivia Martinez', 72000.00),
(9, 'William Anderson', 68000.00),
(10, 'John Doe', 67000.00);
SELECT * FROM employees WHERE name = 'John Doe' ORDER BY id DESC LIMIT 5;

Conclusion

We have explored the various aspects of the LIMIT clause in MySQL. By using the LIMIT clause, you can control the number of records returned, implement pagination, sort results, optimize query performance and make your database queries more efficient.

Copyright ©2024 Educative, Inc. All rights reserved