Limit Clause In MySQL

In MySQL, The LIMIT clause is used to restrict the number of rows returned by a SELECT query.

It is often used for pagination or when we only want to retrieve a specific number of rows from the result set.

Limit Syntax In MySQL:

SELECT column1, column2
FROM tableName
LIMIT number_of_rows;

column1, column2: The columns we want to retrieve from the table.
tableName: The name of the table from which we want to retrieve data.
LIMIT: The keyword used to restrict the number of rows.
number_of_rows: The maximum number of rows to be returned from the table by specifying the Number.

LIMIT Keyword In MySQL:

Let's assume, we want to retrieve the first 5 rows from the orders table.

Example:

SELECT *
FROM orders
LIMIT 5;

The above query retrieves all columns from the orders table but the limit keyword specifies how many records to be returned from the query. In our case it will return 5 rows from the table.

Using OFFSET with LIMIT for Pagination In MySQL:

We can use the "OFFSET" clause along with "LIMIT" to skip a certain number of rows and retrieve the next set of rows. This is commonly used for implementing pagination.

Example:

SELECT *
FROM orders
LIMIT 5 OFFSET 5;

The above query retrieves columns from the `orders` table, but it starts from the 6th row (skipping the first 5 rows) and returns the next 5 rows.

Another Way Example:

SELECT *
FROM orders
LIMIT 5, 5;

The LIMIT clause can be used with or without the OFFSET clause.
The OFFSET value specifies the number of rows to skip before starting to return rows.
Different database systems may have variations in the syntax. For example, in some databases, we might see LIMIT 5 OFFSET 5 written as LIMIT 5, 5.

Sorting Results with Order By and LIMIT Record In MySQL:

It is often useful to order the results before limiting them.

Example:

SELECT * FROM products
ORDER BY product_price DESC
LIMIT 20;

In the above query, we are retrieving records from the `products` table and sorting the result in `Descending` order based on `product_price` and we have applied the limit to 20 to fetch first 20 records from the `products` table.

<-- Exists

Joins -->