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.
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.
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.
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 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.
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;
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.
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;
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.
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.
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;
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.