MYSQL Limit Clause

The “LIMIT” clause in MySQL is used to limit the number of rows returned from a SELECT statement. This can be useful when you only need a subset of the data from a table, for example, when you are paginating the results on a website. The syntax for using the “LIMIT” clause is as follows:

SELECT column_name(s)
FROM table_name
LIMIT number_of_rows;

You can also specify the starting point and number of rows to return by using two parameters in the “LIMIT” clause, like this:

SELECT column_name(s)
FROM table_name
LIMIT starting_point, number_of_rows;

Here, starting_point is the starting row number and number_of_rows is the number of rows to be returned from that point. The first row in a table is considered to be row 0.

For example, if you have a table named “employees” with 100 rows, and you want to retrieve the first 10 rows, you would use the following query:

Table structure Used for Example explanation

+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id               | int(11) unsigned | NO   | PRI | NULL    |       |
| name             | varchar(255)     | NO   | UNI | NULL    |       |
| code             | varchar(3)       | NO   | UNI | NULL    |       |
| population       | int(11)          | NO   |     | NULL    |       |
| currency_code    | varchar(3)       | YES  |     | NULL    |       |
| capital          | varchar(255)     | YES  |     | NULL    |       |
| area             | double           | YES  |     | NULL    |       |
| time_zone        | varchar(255)     | YES  |     | NULL    |       |
| languages        | varchar(255)     | YES  |     | NULL    |       |
| created_at       | datetime         | NO   |     | NULL    |       |
| updated_at       | datetime         | NO   |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+

Example for MYSQL Limit

SELECT Name, Population FROM Country WHERE Population > 100000000 OR (Continent = 'Asia' ) LIMIT 5;