LIMIT with OFFSET in MYSQL

This article will be looking into LIMIT with OFFSET, including the syntax, using examples.

Table of Contents

• What is LIMIT with OFFSET in MySQL.
• Syntax of LIMIT with OFFSET
• Examples of LIMIT with OFFSET

What is LIMIT with OFFSET in MySQL

In MySQL, the LIMIT clause is used to get the limited number of rows in the SELECT query results. LIMIT works with one or two numeric arguments.

The arguments accepted by the LIMIT clause should be nonnegative integer constants.

• LIMIT parameters are specified using ‘?’ in prepared statements.
• LIMIT parameters are specified using local variables with integer values in stored programs.

The first argument is offset value: the offset of the first row is 0 and not 1. This argument is optional.

The second argument is limit value: specifying how many rows to be returned. This argument is mandatory.

Let us look into the below figure 1 to illustrate the working of the LIMIT clause. SELECT * FROM sample_table LIMIT 3,5 

Here, R1, R2 …. represent the records of the table sample_table.

LIMIT clause in MySQL is useful in managing queries on tables with extensive data. Hence, it is mostly used in paginating the query results which further helps in performance optimization.

Syntax of LIMIT with OFFSET

Let us now look into the syntax to use LIMIT with OFFSET.

SELECT [column1, column2,...| ALL] FROM table_references [LIMIT {[offset,] row_count }]

• column1, column2,…| ALL – is the list of fields selected.
• table_references – specifies the references of the table from where the selection is to be made.
• LIMIT {[offset,] row_count } – LIMIT clause with arguments offset and row_count.
• offset- specifies the offset of the first row to return. It should be a nonnegative numeric value.
• row_count – specifies the maximum number of rows to be returned from your result set. It should be a nonnegative numeric value.

Examples of LIMIT with OFFSET

Let us look into a few examples of LIMIT with OFFSET. We will first create a table customer_name_city and insert a few rows into it, which will be used across all examples for demonstration.

CREATE TABLE customer_name_city (
    customer_id INT auto_increment,
    customer_name VARCHAR(255),
    customer_city VARCHAR(255),
	primary key (customer_id)
);
INSERT INTO customer_name_city (customer_name, customer_city) 
VALUES("Gustav","Santiago"),
("Henric","Boston"),
("Richa","Santiago"),
("Margit","Santiago"),	
("Henric","Los Angeles"),
("Henric","Austin"),
("Richa","Washington"),
("Henric","Washington"),
("Rohit","Boston"),
("Barbara","Washington"),
("Susan","Boston");

Let us have a look into the table customer_name_city by executing:

SELECT * FROM customer_name_city;

Examples:-

Get records from 5 to 10 from the customer_name_city table.

SELECT * FROM customer_name_city LIMIT 4,6;

Action Output Message Response:-

6 row(s) returned.

Output:-

We want to exclude the 5th record and only want five total rows in the result set. Observe the change in both the arguments of the LIMIT clause in the below query.

SELECT * FROM customer_name_city LIMIT 5,5;

Action Output Message Response:-

5 row(s) returned.

Output:-

Get the first five rows from the customer_name_city table.

SELECT * FROM customer_name_city LIMIT 5;

Action Output Message Response:-

5 row(s) returned.

Output:-

In the above query, we have not specified the offset value; therefore, the offset is 0. Hence the results are retrieved starting from the initial row.

Specifying 0 as the offset argument value will give the same output.

SELECT * FROM customer_name_city LIMIT 0,5;

Action Output Message Response:-

5 row(s) returned.

Output:-

Output in figure 6 is the same as in figure 5.

We hope this article helped in understanding the concept of LIMIT with OFFSET. Good Luck !!!!