How To Use LIMIT in MySQL?

Introduction

MySQL is a more powerful relational database management system that allows you to store, retrieve, and manipulate data. In this article, one of the key features of MySQL is the ability to use the LIMIT clause to control the number of rows returned by a query. We will explore how to use the LIMIT clause in MySQL with examples and step-by-step instructions.

What is the LIMIT clause in MySQL?

The LIMIT clause is used to limit the number of rows returned by a SELECT statement in MySQL. It is typically used in combination with the ORDER BY clause to specify the order in which the rows should be returned. The syntax for the LIMIT clause is as follows.

SELECT column_name(s) FROM table_name WHERE condition ORDER BY column_name LIMIT offset, count;

The LIMIT clause consists of two parts: the offset and the count. The offset specifies the number of rows to skip before starting to return rows, while the count specifies the maximum number of rows to return.

How to use the LIMIT clause in MySQL?

To demonstrate how to use the LIMIT clause in MySQL, we will use a sample database called "employees" that contains a table called "employees" with the following schema.

CREATE TABLE employees ( id INT PRIMARY KEY, first_name VARCHAR(50), 
last_name VARCHAR(50), email VARCHAR(100), hire_date DATE, salary INT );

insert into employees values(1,'Sachin','Mishra','[email protected]','2023-05-01',12000);
insert into employees values(2,'Harshit','Pandey','[email protected]','2023-05-01',14000);
insert into employees values(3,'Mohit','Mishra','[email protected]','2023-05-01',14000);
insert into employees values(4,'Priyanshu','Agnihotri','[email protected]','2023-05-01',15000);
insert into employees values(5,'Madhu','Patel','[email protected]','2023-05-01',14000);
insert into employees values(2,'simran','verma','[email protected]','2023-05-01',14000);

Output

The table contains information about employees, including their ID, first name, last name, email address, hire date, and salary.

Step 1. Connect to the MySQL server

Before we can run any queries in MySQL, we need to connect to the MySQL server. This can be done using the mysql command-line client or a graphical user interface (GUI) tool such as MySQL Workbench. For this example, we will use the mysql command-line client.

To connect to the MySQL server, open a terminal or command prompt and enter the following command-

mysql -u username -p

Replace "username" with your MySQL username. You will be prompted to enter your password.

Step 2. Select the database

Once you are connected to the MySQL server, you need to select the database that contains the table you want to query. For this example, we will use the "employees" database.

To select the database, enter the following command-

USE employees;

Step 3. Run a simple SELECT statement

Before we can use the LIMIT clause, we need to run a simple SELECT statement to retrieve some data from the "employees" table. For example, the following SELECT statement will retrieve the first name, last name, and salary of all employees-

SELECT first_name, last_name, salary FROM employees;

The output should be a list of all employees' first names, last names, and salaries.

Step 4. Add the LIMIT clause to the SELECT statement

To limit the number of rows returned by the SELECT statement, we need to add the LIMIT clause. For example, the following SELECT statement will retrieve the first 10 rows from the "employees" table-

SELECT first_name, last_name, salary FROM employees LIMIT 4;

Output

The output should be a list of the first 10 employees' first names, last names, and salaries.

Step 5- Use the offset and count parameters in the LIMIT clause

In addition to limiting the number of rows returned by the SELECT statement, we can also use the offset and count parameters in the LIMIT clause to specify which rows to return. For example, the following SELECT statement will retrieve the second 2 rows from the "employees" table.

select * from employees limit 2,2;

Output

Conclusion

The LIMIT clause is a useful feature of MySQL that allows users to limit the number of rows returned by a SELECT statement. This feature is particularly helpful when working with large data sets or when you only need to retrieve a subset of records.

MySQL's syntax for using the LIMIT clause is simple and easy to understand. By specifying a maximum number of rows to return, or an offset and number of rows to return, users can retrieve the data they need quickly and efficiently.

In this article, we have explored how to use the LIMIT clause in MySQL with step-by-step examples and output. Whether you are a beginner or an experienced MySQL user, understanding how to use the LIMIT clause will help you to work more effectively with your data and make your queries more efficient.