For an instructor lead, in-depth look at learning SQL click below.
Understanding how to control the results of your SQL queries can be incredibly useful, especially when dealing with large datasets. One of the most potent clauses you should master is LIMIT, a command used to limit the number of results returned in SQL. This blog post aims to be a comprehensive guide to this command and how to efficiently apply it in various scenarios.
What is LIMIT?
The LIMIT clause is used in SQL to specify the maximum number of records to return from a particular SELECT statement. Frequently used with the SELECT statement, LIMIT is beneficial when dealing with extensive databases and you are interested in a specific amount of data.
Basic Syntax
|
1 2 3 4 5 |
SELECT column1, column2, ... FROM table_name LIMIT number; |
Example Usage
Let’s consider a simple example. Assume we have a table named ‘Employees’ with information about all the employees, and we want to view just the first 5 employees. We would use the LIMIT clause as follows:
|
1 2 3 4 5 |
SELECT * FROM Employees LIMIT 5; |
Note:
The order in which the data is returned depends on the internal arrangement of data in the database, and you may not always get the same order with every execution. To control the order of data, we use the ORDER BY clause.
Combining LIMIT with ORDER BY
Let’s say we want to see the top 5 oldest employees in our database. We would use the LIMIT clause coupled with ORDER BY as follows:
|
1 2 3 4 5 6 |
SELECT * FROM Employees ORDER BY age DESC LIMIT 5; |
Offset in SQL
SQL offers another clause called OFFSET which can be used with LIMIT. The OFFSET clause skips a number of records before beginning to return the records. This is particularly useful when we want to paginate results.
Example Usage
Let’s say we want to see the second set of 5 employees, skipping the first 5. The SQL query would be as shown below:
|
1 2 3 4 5 6 |
SELECT * FROM Employees ORDER BY age DESC LIMIT 5 OFFSET 5; |
That wraps up this post on the LIMIT clause in SQL. With the principles learned in this guide, try and experiment with the LIMIT clause on your datasets and see the magic unfold.