The LIMIT clause in SQL is used to restrict the number of rows returned by a query. It is particularly useful when you want to retrieve only a specific number of records from a large dataset, such as the top 5 highest-paid employees or the first 10 students with the highest scores.

​
Key Points

  1. Restricting Rows: The LIMIT clause specifies the maximum number of rows to return.
  2. Offset Support: You can use the OFFSET keyword to skip a specified number of rows before starting to return rows.
  3. Applicability: The LIMIT clause is commonly used with the SELECT statement.
  4. Performance: Using LIMIT can improve query performance by reducing the amount of data processed and returned.

​
Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;
  • column1, column2, ...: The columns you want to retrieve.
  • table_name: The table from which to retrieve data.
  • number_of_rows: The maximum number of rows to return.

​
Syntax with OFFSET

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET offset_value;
  • offset_value: The number of rows to skip before starting to return rows.

​
Examples

Suppose you have a table named Employees that stores employee details.

Table: Employees

EmployeeIDNameCityStateSalary
1AnandChennaiTamil Nadu50000
2BalaCoimbatoreTamil Nadu60000
3KavithaKaraikalPuducherry55000
4RajMaduraiTamil Nadu70000
5KumarTrichyTamil Nadu45000
6RamSalemTamil Nadu65000
7KarthikTirupatiAndhra Pradesh80000
8DavidBangaloreKarnataka75000
9KannanKannurKerala48000
10SivaWayanadKerala52000

To retrieve the top 5 highest-paid employees:

SELECT Name, City, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;
NameCitySalary
KarthikTirupati80000
DavidBangalore75000
RajMadurai70000
RamSalem65000
BalaCoimbatore60000

​
Using LIMIT with OFFSET

The OFFSET keyword is used to skip a specified number of rows before starting to return rows. For example, to retrieve the next 5 highest-paid employees after the top 5:

SELECT Name, City, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5 OFFSET 5;
NameCitySalary
KavithaKaraikal55000
SivaWayanad52000
AnandChennai50000
KannanKannur48000
KumarTrichy45000

​
Practical Use Case

Suppose you have a table named Students that stores student details, and you want to retrieve the top 3 students with the highest percentages.

Table: Students

StudentIDNameCityStatePercentage
1RamChennaiTamil Nadu92
2KarthikCoimbatoreTamil Nadu88
3DavidBangaloreKarnataka95
4KannanKaraikalPuducherry91
5SivaMaduraiTamil Nadu89
6RameshTrichyTamil Nadu90
7SureshSalemTamil Nadu87
8SathishTirupatiAndhra Pradesh94
9SujathaPalakkadKerala93
10AnandIdukkiKerala85
SELECT Name, City, Percentage
FROM Students
ORDER BY Percentage DESC
LIMIT 3;
NameCityPercentage
DavidBangalore95
SathishTirupati94
SujathaPalakkad93

​
Combining LIMIT with WHERE Clause

You can combine the LIMIT clause with the WHERE clause to filter and restrict the number of rows returned. For example, retrieve the top 2 students from Tamil Nadu with the highest percentages.

SELECT Name, City, Percentage
FROM Students
WHERE State = 'Tamil Nadu'
ORDER BY Percentage DESC
LIMIT 2;
NameCityPercentage
RamChennai92
RameshTrichy90

​
Key Takeaways

  1. The LIMIT clause restricts the number of rows returned by a query.
  2. It is often used with ORDER BY to retrieve the top or bottom records.
  3. The OFFSET keyword skips a specified number of rows before returning results.
  4. Combining LIMIT with WHERE allows you to filter and restrict rows simultaneously.
  5. Using LIMIT improves query performance by reducing the amount of data processed.