MySQL LIMIT Clause

MySQL consists of various clauses for performing efficient data retrieval, and the LIMIT clause is essential for controlling the number of rows returned in a query. By specifying a limit on the number of results, the LIMIT clause helps manage large datasets, optimize performance, and focus on relevant data. This article will explore the LIMIT clause, showing its usage in terms of examples.

MySQL LIMIT Clause

The LIMIT clause in MySQL is used to specify the maximum number of rows returned by a query. It is particularly useful for managing large result sets and optimizing query performance. By setting a row limit, you can control the amount of data retrieved and efficiently paginate through results.

Syntax:

The syntax for using LIMIT Clause in MySQL is as follows:

SELECT column_name(s)
FROM table_name
LIMIT [offset,] row_count;

Parameters:

• offset (optional): The starting point from which rows are returned. If omitted, it defaults to 0.
• row_count: The maximum number of rows to return.

MySQL LIMIT Clause Example

Let’s look at some examples of the LIMIT Clause in MySQL. Learning the LIMIT Clause with examples will help in understanding the concept better.

First, let’s create a table:

Demo MySQL Database

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, name, salary) VALUES
(1, 'Gaurav', 75000.00),
(2, 'Yuvraj', 82000.00),
(3, 'Shruti', 69000.00),
(4, 'Anjali', 90000.00),
(5, 'Vaishnavi', 85000.00),
(6, 'Gauri', 78000.00);

SELECT * FROM employees;

Output:

+-------------+-----------+----------+
| employee_id | name      | salary   |
+-------------+-----------+----------+
|           1 | Gaurav    | 75000.00 |
|           2 | Yuvraj    | 82000.00 |
|           3 | Shruti    | 69000.00 |
|           4 | Anjali    | 90000.00 |
|           5 | Vaishnavi | 85000.00 |
|           6 | Gauri     | 78000.00 |
+-------------+-----------+----------+

Example 1: Retrieve the First 3 Records

In this example, we are using the LIMIT clause to fetch the first 3 records from the employees table. This query returns the earliest entries based on their order in the table.

SELECT * FROM employees
LIMIT 3;

Output:

+-------------+--------+----------+
| employee_id | name   | salary   |
+-------------+--------+----------+
|           1 | Gaurav | 75000.00 |
|           2 | Yuvraj | 82000.00 |
|           3 | Shruti | 69000.00 |
+-------------+--------+----------+

Example 2: Retrieve 3 Records Starting from the 2nd Record

In this example, we are using the LIMIT clause with an offset to skip the first record and return the next 3 records from the employees table. This allows us to paginate through the results by specifying a starting point.

SELECT * FROM employees
LIMIT 1, 3;

Output:

+-------------+--------+----------+
| employee_id | name   | salary   |
+-------------+--------+----------+
|           2 | Yuvraj | 82000.00 |
|           3 | Shruti | 69000.00 |
|           4 | Anjali | 90000.00 |
+-------------+--------+----------+

Example 3: Retrieve the Last 2 Records

In this example, we are using the LIMIT clause in combination with ORDER BY to fetch the last 2 records from the employees table. Sorting the results in descending order and limiting the output lets us obtain the most recent entries.

SELECT * FROM employees
ORDER BY employee_id DESC
LIMIT 2;

Output:

+-------------+-----------+----------+
| employee_id | name      | salary   |
+-------------+-----------+----------+
|           6 | Gauri     | 78000.00 |
|           5 | Vaishnavi | 85000.00 |
+-------------+-----------+----------+

Conclusion

In conclusion, the LIMIT clause in MySQL is a useful clause for controlling the number of rows returned by a query. It enables efficient data management by allowing you to focus on a subset of records, optimize performance, and paginate results. Understanding and using LIMIT can significantly enhance your ability to handle large datasets effectively.