When working with large datasets in MySQL, it is often necessary to retrieve only a specific subset of the data. This is where the “LIMIT” and the “OFFSET” clauses come into play. The LIMIT clause specifies the total number of rows to be retrieved, while the OFFSET clause is utilized to specify the number of rows to be skipped from the result set. With these clauses, the user can paginate their results and retrieve only the data they require.
In this guide, we will explore the syntax along with various use cases of the LIMIT and OFFSET clauses in MySQL.
How to Use LIMIT and OFFSET Clauses in MySQL?
By using the “LIMIT” and “OFFSET” clauses with the “SELECT” statement in MySQL, the user can specify the maximum number of rows to be returned, and indicate where to begin the result set. Before moving toward the use of LIMIT and OFFSET clauses in MySQL, let’s understand their syntax.
The following syntax can be utilized to use the “LIMIT” clause with the “SELECT” statement:
FROM [table_name]
LIMIT [number_of_rows];
In the above syntax, the “[column1]” and “[column2]” are the column names that are being used with the “SELECT” statement. Specify the table name in place of the “[table_name]”. After that, use the “LIMIT” keyword followed by the number of rows to be fetched.
The following is the syntax to use the “OFFSET” clause with the “LIMIT” clause and “SELECT” statement:
FROM [table_name]
LIMIT [number_of_rows] OFFSET [offset_value];
In the above syntax, the “OFFSET” clause is being used with the “LIMIT” clause to indicate where to begin the result set.
Let’s move to the examples to understand the use case of the “LIMIT” and “OFFSET” clauses in MySQL.
Example 1: Using LIMIT Clause
The “LIMIT” clause can be utilized with the “SELECT” statement to get the limited number of rows from a specific table, as shown below:
LIMIT 5;
In the above example, the “customers_data” table is being used to get its first 5 rows.
Output
The output showed the first 5 rows of the “customers_data” table, which were limited by the “LIMIT” clause.
Example 2: Using LIMIT With a WHERE Clause
Use the “LIMIT” clause along with the “WHERE” clause to get the limited number of rows based on a specific condition. An example of using the “LIMIT” clause with the “WHERE” clause is given below:
WHERE id > 10
LIMIT 10;
In the above example, the condition is to only consider the row that has an “id” greater than 10.
Output
The output depicts that the condition has been applied.
Example 3: Using OFFSET With LIMIT Clause to Skip Rows
To skip a specific set of rows while getting the result set of the “SELECT” statement, use the “LIMIT” clause along with the “OFFSET” clause. The example to skip the first 3 rows and getting only 5 rows is given below:
LIMIT 5 OFFSET 3;
Output
The output showed that the first 3 rows had been skipped and the subsequent 5 rows had been retrieved.
Example 4: Using LIMIT Clause With Multiple Values
Specify multiple values with the “LIMIT” clause to limit the number of returned rows after skipping a particular number of rows. The example of getting the 4 rows after skipping the first 2 rows is given below:
LIMIT 2, 4;
Output
The output displayed that the first 2 rows have been skipped and the next 4 rows have been fetched.
Conclusion
The LIMIT and OFFSET clauses are very useful in MySQL for limiting the number of rows returned by a SELECT statement and indicating where to begin the result set. The LIMIT clause is utilized with the SELECT statement to get the limited number of rows from a specific table, while the OFFSET clause is used to skip a particular number of rows before returning the result set. These clauses can be operated together or separately with the SELECT statement, and they offer flexibility and control over the result set returned by the query. This guide has provided information on the use cases of the LIMIT and OFFSET clauses of MySQL.
