MySQL : LIMIT Clause

In MySQL, the `LIMIT` clause is used to restrict the number of rows returned by a query. It is commonly used with the SELECT statement to limit the number of rows returned, especially when dealing with large datasets. Here's the basic syntax of the LIMIT clause:


SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
LIMIT number_of_rows;
 

  • column1, column2, ...: The columns you want to retrieve data from.
  • table_name: The name of the table you want to retrieve data from.
  • WHERE condition: Optional clause to specify conditions for selecting rows.
  • ORDER BY column_name [ASC | DESC]: Optional clause to specify the order in which rows should be returned. By default, it sorts in ascending order ( ASC ). You can use DESC for descending order.
  • LIMIT number_of_rows: Specifies the maximum number of rows to be returned by the query.

Here are some examples to illustrate how the LIMIT clause works:

1. Retrieve the first 10 records from the `employees` table:

   SELECT * FROM employees LIMIT 10;
   

2. Retrieve the first 5 records from the `customers` table where the `country` is 'USA':

   SELECT * FROM customers WHERE country = 'USA' LIMIT 5;
 

3. Retrieve the highest 3 salaries from the `employees` table in descending order:
   
   SELECT * FROM employees ORDER BY salary DESC LIMIT 3;
   

4. Retrieve the second and third lowest salaries from the `employees` table:
 
   SELECT * FROM employees ORDER BY salary ASC LIMIT 1, 2;
   

In the last example, 1, 2 after the LIMIT keyword means to skip the first row and then return the next two rows. This is useful for paginating results, where you want to display results in chunks.

The LIMIT clause is particularly helpful for optimizing query performance and managing result sets, especially when dealing with large datasets or when implementing pagination in web applications.