LIMIT clause

Sometimes tables contain thousands of rows and if you run a SELECT statement to display all rows, you will impact performance and possibly even crash the system. However, you can use the LIMIT clause to choose how many rows will be returned in a query. Here is an example:

mysql> SELECT * FROM employees LIMIT 5;
 +--------+------------+------------+-----------+--------+------------+
 | emp_no | birth_date | first_name | last_name | gender | hire_date |
 +--------+------------+------------+-----------+--------+------------+
 | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
 | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
 | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
 | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
 | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
 +--------+------------+------------+-----------+--------+------------+
 5 rows in set (0.01 sec)

The employees table mentioned above contains thousands of rows. Using the LIMIT clause, we were able to display only the first 5 rows.

You can also specify the starting position. For example, to start at the position 2 and return 7 rows, we can use the following command:

mysql> SELECT * FROM employees LIMIT 2,7;
 +--------+------------+------------+-----------+--------+------------+
 | emp_no | birth_date | first_name | last_name | gender | hire_date |
 +--------+------------+------------+-----------+--------+------------+
 | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
 | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
 | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
 | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
 | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
 | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
 | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
 +--------+------------+------------+-----------+--------+------------+
 7 rows in set (0.00 sec)

 

Notice that the second row in the employees table was not included in the output above. The LIMIT 2,7 keyword means return seven rows starting from the third row.
Geek University 2021