SQL LIMIT Clause Example

1. Introduction

  In this article, we will look at how to use the SQL LIMIT clause using various examples using MySQL RDBMS.

1.1 What is SQL?

SQL is also called “sequel” and it stands for Structured Query Language. SQL first appeared in 1974 and Donald Chamberlin and Robert Boyce designed it.

It is based upon relational algebra and tuple-relational calculus and is the programming language for programming and designing data stored in a Relational database management system (RDBMS).

Depending upon the RDBMS used, the SQL can have dialects. Oracle uses PL/SQL and MS SQL Server uses T-SQL and MySQL uses SQL.

1.2 Setup

The setup we use for running examples is as follows:

• MySQL Community Server version 8.0.22. To install, please visit the MySQL Community Downloads page. The Documentation is available here.
• Workbench for running our queries which comes as a part of the community server download. Its documentation is available here.
• SAKILA Database is provided by MySQL itself. The documentation provides details about the structure, installation steps(if any), Schema details, etc.

2. LIMIT Clause

In this article, we will look at one of the SQL clauses called the Limit Clause. The LIMIT clause is used with the select clause is the last clause to execute. Its main purpose is to limit the number of records returned by the SELECT query.

2.1 Syntax

The LIMIT has 2 arguments. Both these arguments need to be 0 or some positive integer.

SELECT column_names
FROM table_name(s)
Where [condition(s)]
Order by [expressions [ASC| DESC]]
LIMIT [offset,] row_count; 

offset: The offset is an optional argument. Its default value is 0.

row_count: This argument tells us how many rows need to be returned.

The default offset value is 0 and so the following 2 lines are equivalent

SELECT column_names
FROM table_name(s)
LIMIT 0, 10; 

AND

SELECT column_names
FROM table_name(s)
LIMIT 10;

Here the row_count =10 i.e. the output has 10 lines starting with the first record.

3. Syntax in Dialects

The MySQL RDBMS uses the LIMIT Clause syntax as-is. Depending upon the RDBMS used, the syntax changes a little. Different RDBMs use different syntax

3.1 PostgreSQL

PostgreSQL interchanges the 2 arguments, and we have to specify the keyword OFFSET to give one.

SELECT column_name(s)
FROM table_name(s)
LIMIT row_count OFFSET offset;

3.2 Oracle

In Oracle we use a dialect called PL/SQL. In PL/SQL we use the Rownum clause to limit the number of records.

SELECT column_name(s)
FROM table_name(s)
WHERE ROWNUM <= number;  

number = number of records to be returned.

3.3 SQL server

One other syntax clause that is equivalent to LIMIT clause in mySQL is the TOP Clause. It is used in the SQL Server RDBMS

SELECT TOP number
FROM table_name
WHERE ROWNUM <= number;

number = number of records to be returned.

4. Usage Examples

For understanding the LIMIT Clause in action, we will consider a scenario for a website

4.1 The Scenario/Problem Statement

Assume we have two dvd-rental stores. We wish to show all the films rented by a particular customer. There is a table which can show only 10 records at a time on the page.

4.1.1 Data

We will consider the database SAKILA. The tables used are PAYMENT, RENTAL, INVENTORY and FILM and the customer_id is 148. This customer has 46 records. Shown below are all the records for the customer 148

Query used

SELECT ROW_NUMBER() OVER (ORDER BY F.TITLE) RECORD_NUMBER
,F.TITLE, F.DESCRIPTION,F.LENGTH,F.RATING,P.AMOUNT,P.PAYMENT_DATE,I.STORE_ID
FROM PAYMENT P,RENTAL R, INVENTORY I,FILM F
WHERE P.CUSTOMER_ID = R.CUSTOMER_ID
AND   P.RENTAL_ID = R.RENTAL_ID
AND  R.INVENTORY_ID = I.INVENTORY_ID
AND F.FILM_ID = I.FILM_ID
AND P.CUSTOMER_ID = 148;

4.1.2 LIMIT without Offset /LIMIT OFFSET TO DEFAULT(0)

For showing the First 10 records, we can use the LIMIT clause without the OFFSET clause and so we need the first 10 records i.e. records 1 to 10. The rowcount mentioned in the LIMIT is included in the records returned. Example if the rowcount is 10 then the output is 10 records.

SELECT ROW_NUMBER() OVER (ORDER BY F.TITLE) RECORD_NUMBER
,F.TITLE, F.DESCRIPTION,F.LENGTH,F.RATING,P.AMOUNT,P.PAYMENT_DATE,I.STORE_ID
FROM PAYMENT P,RENTAL R, INVENTORY I,FILM F
WHERE P.CUSTOMER_ID = R.CUSTOMER_ID
AND   P.RENTAL_ID = R.RENTAL_ID
AND  R.INVENTORY_ID = I.INVENTORY_ID
AND F.FILM_ID = I.FILM_ID
AND P.CUSTOMER_ID = 148
LIMIT 10;

The query above is equivalent to saying offset = 0 and row_count = 10 i.e. the query above is like writing LIMIT 0,10

4.1.3 LIMIT WITH OFFSET

To get the next 10 records, we would require the below given Query. The output always starts from one record after the offset value. For example: if the offset given is 10, then the 11^th record onwards will be returned. Essentially Offset is like saying “give me the records after the mentioned row number”. Visually we can think of this as follows:

Query to get the next 10 records is as follows

SELECT ROW_NUMBER() OVER (ORDER BY F.TITLE) RECORD_NUMBER
,F.TITLE, F.DESCRIPTION,F.LENGTH,F.RATING,P.AMOUNT,P.PAYMENT_DATE,I.STORE_ID
FROM PAYMENT P,RENTAL R, INVENTORY I,FILM F
WHERE P.CUSTOMER_ID = R.CUSTOMER_ID
AND   P.RENTAL_ID = R.RENTAL_ID
AND  R.INVENTORY_ID = I.INVENTORY_ID
AND F.FILM_ID = I.FILM_ID
AND P.CUSTOMER_ID = 148
LIMIT 10,10;

5. Summary

In this article, we saw how to use the LIMIT CLAUSE in MySql using examples.

6. Download the Source Code

This was an example of the SQL LIMIT Clause using MySQL RDBMS.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.