This tutorial explains how to use the MySQL LIMIT clause and LIMIT with OFFSET with easy to understand practical examples:
The MySQL LIMIT clause is a widely used tool or option that could be specified along with the SELECT queries to control which rows are output.
In the most basic form, it allows controlling the number of rows returned through the output of the query.
There are other variations of using the LIMIT command like using MySQL LIMIT OFFSET which we would see with examples in this tutorial.
=> Check Out The Perfect MYSQL Training Guide Here
Table of Contents:
MySQL LIMIT
Test Data
We will have 2 tables namely employee id and department id.
Please refer to the schema and test data below.
-- table creation - employee_department
CREATE TABLE employee_department(dept_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, dept_name VARCHAR(100));
-- table creation - employees
CREATE TABLE employees (emp_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, emp_name VARCHAR(150) DEFAULT NULL,
dept_id SMALLINT, CONSTRAINT deptIdFk FOREIGN KEY(dept_id)
REFERENCES employee_department(dept_id) ON UPDATE CASCADE ON DELETE CASCADE);
-- data insertion employee_department
INSERT INTO employee_department(dept_name) VALUES ("HR"),("ENGINEERING"),("SALES"),("MARKETING");
-- data insertion employees
INSERT INTO employees(emp_name, dept_id) VALUES
("Damian Kim",1),("Tara Vargas",2),("Marlon Jenning",4),("Leopold Schuman",2),("Bailey Sutton",3),("Toby Roffe",4),("Troy Duncan", 1),("Polly Berry",2),("Dorian Maillin", 2),("Thora Mason",3),("Taylor Morales",4),("Ida Carr",2);
Syntax
SELECT {column name(s)} FROM {table_name} LIMIT [offset] row_count;
- {column_name} represents the selected columns that need to be returned as query output.
- {table_name} is the name of the table on which the query is being executed.
- [offset] is an optional parameter that specifies the first row to be returned. It’s a 0 based index (and the default value of offset unless specified is 0).
- row_count specifies the no of rows to be returned in the result set.
A simple LIMIT query with the table data that we have could be as below.
Suppose we want to get the first 5 records from the employee’s table, then we could use something like
SELECT * FROM employees LIMIT 5;
In the above output, you can see that the first 5 records have been returned with the use of the LIMIT clause in the SELECT Query.
Using LIMIT with OFFSET
At times we have to mention the offset and no. of rows to be returned by the LIMIT clause.
For example: Starting at offset 5, I want to see the next 3 records.
For such requirements, we can use LIMIT with OFFSET.
SELECT * FROM employees LIMIT 5,3;
In the above output, you can see that we have got 3 records starting from the offset.
Here the order of records would depend on the PRIMARY KEY being used in the table and the default SORT ORDER being used (which is ascending).
We can also alter the order, and sequence of records by sorting against other columns or criteria.
For example:
SELECT * FROM employees ORDER by emp_id desc LIMIT 4,3
Would return the next 3 records, counting from the descending order from the top. So out of the total of 12 records – It will exclude the first 5 records (starting 0) and print the next 3 records.
Other Examples of Using MySQL LIMIT
Using MySQL LIMIT with JOINED TABLES
MYSQL LIMIT can also be used to LIMIT the no of records in JOIN queries.
For example – Let’s fetch the department name using INNER JOIN and LIMIT the results to 5 records.
SELECT emp.emp_name, emp_dept.dept_name from employees emp INNER JOIN employee_department emp_dept ON emp.dept_id = emp_dept.dept_id LIMIT 5
The query would LIMIT the results to 5 and return the first 5 records with emp_name and dept_name
- MySQL LIMIT with GROUP BY / ORDER BY
Let’s see an example using LIMIT with GROUP BY
SELECT emp_dept.dept_name, COUNT(emp.dept_id) as total_employees from employees emp INNER JOIN employee_department emp_dept ON emp.dept_id = emp_dept.dept_id GROUP BY emp.dept_id LIMIT 2
We are extending the same query to GROUP BY department name and return the count of the first 2 departments.
Let’s sort the same query using ORDER BY to get the records of the top 2 departments sorted by count.
SELECT emp_dept.dept_name, COUNT(emp.dept_id) as total_employees from employees emp INNER JOIN employee_department emp_dept ON emp.dept_id = emp_dept.dept_id GROUP BY emp.dept_id ORDER BY total_employees desc LIMIT 2
MySQL LIMIT With DISTINCT
LIMIT can also be used along with the DISTINCT clause.
In this case, whenever the MySQL Engine finds the no of records to be returned, it stops further processing and returns the result set.
In this case, we are using the DISTINCT clause to return employees with DISTINCT names.
SELECT DISTINCT(emp_name) FROM employees LIMIT 5
Applications of LIMIT CLAUSE
LIMIT clause is used to reduce the IO/data over the network in cases where there are tables having millions of records and we are interested in displaying only a small number of records.
For example: Consider Google search to be a SELECT QUERY. So on a web browser, you just see 15-20 results and see the rest of the results on the next pages.
If it were to load all thousands of results during the single execution, then it would have been an expensive IO call as well as not very useful for the user.
So in general, the advantages of using LIMIT would include:
- Enhancing the performance of the web applications.
- Making the applications more user-friendly/readable for customers.
- LIMIT queries are also faster. For example, when LIMIT is used along with ORDER BY, then as soon as the no of records expected is reached, the MySQL engine would stop the sorting (as opposed to full sort without a LIMIT statement).
Frequently Asked Questions
Q #1) What is LIMIT in MySQL?
Answer: MySQL provides a LIMIT clause to restrict the count of rows returned in the result set. This helps in optimizing the Network IO and results in faster query executions.
The LIMIT clause is used along with the SELECT query.
Example:
SELECT * from employees LIMIT 5
The above query would return the first 5 records from the employees limit.
Q #2) How does LIMIT optimize the SQL Queries?
Answer: MySQL LIMIT Optimise the SQL queries depending on the count of rows to be fetched and returned. For example, a table containing a million rows and a LIMIT query needs the first 50 records sorted by NAME – the MySQL engine would stop processing as soon as it reaches the 50th record.
So, overall for large data sets, the LIMIT clause can greatly enhance the performance of the server depending on the data queried for.
Also from the User Experience perspective – for example applications like record search against criteria – the concept of pagination is used wherein a single page or screen a fixed number of records are shown. In such cases, the MySQL LIMIT clause with OFFSET can be used.
For example:
SELECT * from employees LIMIT 50,10
The above query would return the next 10 records starting from an offset value of 50.
Conclusion
In this tutorial, we learned about using the MySQL LIMIT clause in the SELECT queries.
The LIMIT clause can greatly enhance the database server performance as well as faster and responsive applications by fetching the data it needs rather than fetching the full data sets.
The LIMIT clause can also be used with other options like OFFSET where a specified no. of rows is returned starting from a specified offset.
=> Explore The Simple MYSQL Training Series Here