LIMIT Clause in SQL

The LIMIT clause in SQL is used to restrict the number of rows returned by a query. It is especially useful for pagination or when you want to retrieve only a specific subset of the results.

Key Points

Restricts Result Set Size:
The LIMIT clause specifies the maximum number of rows to return, making it ideal for handling large datasets.
Often Used with OFFSET:
To skip a specified number of rows before starting to return rows, the OFFSET keyword is often used alongside LIMIT.

Syntax

Basic Syntax

sqlCopyEditSELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

With OFFSET

sqlCopyEditSELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET start_position;

Example

Fetching the First Few Rows

sqlCopyEditSELECT first_name, last_name
FROM employees
LIMIT 5;

Retrieves the first 5 rows from the employees table.

Skipping Rows with OFFSET

sqlCopyEditSELECT first_name, last_name
FROM employees
LIMIT 5 OFFSET 10;

Skips the first 10 rows and retrieves the next 5 rows.

Using LIMIT for Pagination

For a typical pagination scenario:

Page 1:

  sqlCopyEditSELECT first_name, last_name
  FROM employees
  LIMIT 10 OFFSET 0;

Page 2:

  sqlCopyEditSELECT first_name, last_name
  FROM employees
  LIMIT 10 OFFSET 10;

Combining with ORDER BY

sqlCopyEditSELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Returns the top 3 highest-paid employees by sorting salaries in descending order.

When to Use the `LIMIT` Clause

To fetch a manageable subset of rows from a large dataset.
To implement pagination in applications.
To preview data without loading the entire table.

Note: Some databases (like SQL Server) use TOP instead of LIMIT, or FETCH FIRST n ROWS ONLY. Always check the syntax for the specific database system.