LIMIT Clause in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

LIMIT Clause in MySQL with Examples

In this article, I am going to discuss the LIMIT Clause in MySQL with Examples. Please read our previous article where we discussed the Order by Clause in MySQL with examples.

LIMIT or TOP Clause in MySQL

The LIMIT Clause in MySQL is used to retrieve the records from one or more database tables and then limit the number of records returned based on a given value.

The LIMIT or TOP clause is useful to specify the number of data rows to return. In large tables with thousands or millions of data rows, it takes some time to return all the records, which cause database performance issue. To fix this problem, we can return the specified number of data rows from a table. The LIMIT OR TOP Clause SQL statement varies for different database systems. Here are some examples of LIMIT or TOP clause in SQL statement depending upon the database systems.

SELECT TOP 5 FROM employee; (SQL server or MS Access)
SELECT * FROM employee WHERE ROWNUM <=; (oracle)
SELECT * FROM employee LIMIT 5; (MySQL)

Example to understand LIMIT Clause in MySQL:

We are going to use the following Employee table to understand the LIMIT Clause in MySQL with Examples.

Please use the following SQL Script to create the company database and employee table with the required records.

CREATE DATABASE company;
USE company;

CREATE TABLE employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1011, 'Pramod Panda', 'IT', 45000, 'Male', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1012, 'Preety Tiwary', 'HR', 55000, 'Female', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1013, 'Santosh Dube', 'IT', 52000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1014, 'Manoj Tripathy', 'HR', 85000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1015, 'Mitali Rout', 'Finance', 70000, 'Female', 26, 'Mumbai');

Example:

Let’s only display 5 data rows from the employee table. The following is the SQL statement using the LIMIT Clause to display the top 5 records from the employee table.

SELECT * FROM Employee LIMIT 5;

Once you execute the above statement, it will display the following result set.

Example: LIMIT Clause with Where Clause in MySQL

In the following SQL query, first, it filters the data based on the CITY Column and then retrieves the top 3 employees using LIMIT Clause from the employee table.

SELECT * FROM Employee WHERE City = ‘Mumbai’ LIMIT 3;

When you execute the above statement, it will give you the following result set.

Example: LIMIT Clause with Where and Order by Clause in MySQL

In the following SQL query, first, it filters the data based on the CITY Column and then sorts the data based on the Name and finally retrieves the top 3 employees from the employee table.

SELECT * FROM Employee WHERE City = ‘Mumbai’ ORDER BY Name ASC LIMIT 3;

Once you execute the above SQL Statement, you will get the following result set.

In the next article, I am going to discuss Alias in MySQL with Examples. Here, in this article, I try to explain the LIMIT or TOP Clause in MySQL with Examples. I hope you enjoy this article. I would like to have your feedback. Please post your feedback, question, or comments about this article.